Forum Discussion
Need help on VBA code
This code's purpose is needs to print if there is a 'True' in A column, it should print the corresponding part in D column in order. Every time it's printing the first item in the D column to the C column which is not I want. There is something wrong with this code.
Attached files below xlsx is original one, xlsm file is formatted one. İn xlsx there is a sample line in 12 to show how it should be.
Can somebody help me?
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
End If
Next i
If s <> "" Then
SelItems = Mid(s, Len(sep) + 1)
End If
End Function
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.
8 Replies
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 FunctionTo 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
- This is working absolutely fine. Thank you so much for your time. I appreciate that.
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.
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?
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.
