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.
Option Explicit
Function SelItems(rng1, rng2) As String
Const sep = vbLf
Dim a1() As String
Dim a2() As String
Dim i As Long
Dim j 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(j)
j = j + 1
Else
j = j + 1
End If
Next i
If s <> "" Then
SelItems = Mid(s, Len(sep) + 1)
End If
End Function
To me it seems that you simply have to add the else part of the if then else statement. With this the formula returns the expected result in my spreadsheet.