Nov 24 2021 11:24 PM - edited Nov 24 2021 11:30 PM
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
Nov 25 2021 12:27 AM - edited Nov 25 2021 12:29 AM
Solution@Meceka Tested the UDF that you received earlier from @Hans Vogelaar 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.
Nov 25 2021 12:45 AM - edited Nov 25 2021 01:04 AM
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?
Nov 25 2021 01:07 AM
@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.
Nov 25 2021 01:22 AM
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.
Nov 25 2021 01:41 AM
Nov 25 2021 02:29 AM
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
Nov 25 2021 04:39 AM
Nov 25 2021 04:44 AM - edited Nov 25 2021 04:45 AM
@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.
Nov 25 2021 12:27 AM - edited Nov 25 2021 12:29 AM
Solution@Meceka Tested the UDF that you received earlier from @Hans Vogelaar 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.