SOLVED

Need help on VBA code

Copper Contributor

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

8 Replies
best response confirmed by Meceka (Copper Contributor)
Solution

@Meceka Tested the UDF that you received earlier from @Hans Vogelaar here:

https://techcommunity.microsoft.com/t5/excel/need-help-with-copy-values-in-comma-seperated-data-into... 

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:

https://techcommunity.microsoft.com/t5/excel/how-to-copy-values-in-comma-seperated-data-into-another... 

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.

@Meceka 

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.

This is working absolutely fine. Thank you so much for your time. I appreciate that.

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

@Meceka 

This is probably what you want to do. 

I only changed on line of code to:

s = s & "," & a2(j)

@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.

1 best response

Accepted Solutions
best response confirmed by Meceka (Copper Contributor)
Solution

@Meceka Tested the UDF that you received earlier from @Hans Vogelaar here:

https://techcommunity.microsoft.com/t5/excel/need-help-with-copy-values-in-comma-seperated-data-into... 

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:

https://techcommunity.microsoft.com/t5/excel/how-to-copy-values-in-comma-seperated-data-into-another... 

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.

 

View solution in original post