Forum Discussion
Need help on VBA code
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 Functionand 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.
Hi, OliverScheurich I wonder if there is a way to add commas between each other in column C instead of line break and space? Because I will need to export it as a CSV file. Please see attached image as an example
OliverScheurich Thank you for your kind answer. Now I get real data. And its different than the example one. Now we need to get values between B6 to B11 to print those 6 lines 'True' values into column C as you did before.
What can I do here? This is final round of test.
