Forum Discussion
Need help on VBA code
- Nov 25, 2021
Meceka Tested the UDF that you received earlier from HansVogelaar here:
and it seems the the first line-feed in each of the Option strings is messing up things. Change the code to this:
Option Explicit Function SelItems(rng1, rng2) As String Const sep = vbLf Dim a1() As String Dim a2() As String Dim i As Long Dim s As String a1 = Split(rng1, vbLf) a2 = Split(rng2, ",") For i = UBound(a1) - 5 To UBound(a1) If a1(i) = "True" Then s = s & sep & a2(i - 1) i = i + 1 End If Next i If s <> "" Then SelItems = Mid(s, Len(sep) + 1) End If End Function
and I believe it works as desired.
Alternatively, consider changing the set-up of your schedule as suggested to you by @Jan Karel Pieterse in this thread:
However, the Data, From Table wouldn't work for you since you are on a Mac. But there are other ways and than no VBA coding is needed. If you are on MS365 or Excel 2021 you could have a look at the attached file.
Meceka Tested the UDF that you received earlier from HansVogelaar here:
and it seems the the first line-feed in each of the Option strings is messing up things. Change the code to this:
Option Explicit
Function SelItems(rng1, rng2) As String
Const sep = vbLf
Dim a1() As String
Dim a2() As String
Dim i As Long
Dim s As String
a1 = Split(rng1, vbLf)
a2 = Split(rng2, ",")
For i = UBound(a1) - 5 To UBound(a1)
If a1(i) = "True" Then
s = s & sep & a2(i - 1)
i = i + 1
End If
Next i
If s <> "" Then
SelItems = Mid(s, Len(sep) + 1)
End If
End Function
and I believe it works as desired.
Alternatively, consider changing the set-up of your schedule as suggested to you by @Jan Karel Pieterse in this thread:
However, the Data, From Table wouldn't work for you since you are on a Mac. But there are other ways and than no VBA coding is needed. If you are on MS365 or Excel 2021 you could have a look at the attached file.
Hi Riny_van_Eekelen , thank you for your answer. İn this attached image below, there are 4 true in column A but just 2 of them printing in column C with your code.
Do you have any idea about that?
- Riny_van_EekelenNov 25, 2021Platinum Contributor
Meceka Can't tell. Perhaps not all "True" entires in column A are exactly the same. And the output doesn't seem to be coming from the UDF, as I would expect a comma separating the items.