VBA Problems with Concatenating String

Question

I am trying to assign each of the ID's you see in column E and F of ws4 here...

...to the respective ID on my wsOutput in column K and L, respectively.

My code runs through without an Error but nothing happens. This is one of my first projects, so I apologize if this is straight-forward question.

I also consulted the Internet and found:

However, I wasn't able to get their approaches working.

Any help is greatly appreciated!

'Previous Code
'wsOutput -> Filter Sheet - Worksheet (TARGET) ; ws4 = Search Skills - Worksheet (SOURCE)
Dim separator As String, PreviousResultCG As String, NewResultCG As String, PreviousResultCategory As String, NewResultCategory As String

If separator = "" Then separator = " , "

'lRowInput = ws4.Range("A" & Rows.Count).End(xlUp).row - from above
lRowOutput = wsOutput.Range("A4:A" & Rows.Count).End(xlDown).row


With ws4

    'For each ID on the Source-Worksheet
    For Each ID In .Range("A2:A" & lRowInput)

        'Find the respective ID on Target-Worksheet
        Set FindID = wsOutput.Range("A4:A" & lRowOutput).Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)

        'Get all CG ID's for the supplier and add them to previously found ID's of that supplier
        If FindID = ID Then

            PreviousResultCG = wsOutput.Range("K" & FindID.row).value

            NewResultCG = PreviousResultCG & separator & .Range("E" & ID.row)

            wsOutput.Range("K" & ID.row).value = NewResultCG


            PreviousResultCategory = wsOutput.Range("L" & FindID.row).value

            NewResultCategory = PreviousResultCategory & separator & .Range("F" & ID.row)

            wsOutput.Range("L" & FindID.row).value = NewResultCategory

        End If

    Next ID

End With

Thank you for your answer. This is Excel, yes. I wouldn't know how vlookup would solve my problem. However, as I hinted above, I am a beginner in VBA. — InternInNeed
– InternInNeed, Commented Sep 29, 2016 at 8:13
VLOOKUP is a built-in function in Excel which will allow you to do what you describe above. Unless there are other requirements you need to meet, I suggest you try the built-in function first. — L42
– L42, Commented Sep 29, 2016 at 8:20
What do you mean with "other requirements" ? My only Problem is that the Input-Worksheet is not fixed but rather get's re-populated when a Button is clicked. Also: The number of entries will vary every time. That's why I tried to automate it. — InternInNeed
– InternInNeed, Commented Sep 29, 2016 at 8:24
Oh, and VLOOKUP does not put all the different ID's into one cell, right? — InternInNeed
– InternInNeed, Commented Sep 29, 2016 at 8:26

Aditya Pansare · Accepted Answer · 2016-09-29 09:27:14Z

Place source data in sheet named "source" and create another sheet where you want to lookup values from source data named as "target". Keep columns as you shown in images.

paste below mentioned code in module.

Sub look_values()

Dim id, source_id As Range
Dim data_row_num, id_row_num As Long
Dim source_sheet, target_sheet As Worksheet
Dim cg, cat As String

Set source_sheet = ThisWorkbook.Sheets("source")
Set target_sheet = ThisWorkbook.Sheets("target")
Set id = target_sheet.Range("A2")

Do Until id.Value = ""

    source_sheet.Activate
    Range("A1").Activate
    Set source_id = Range("A:A").Find(what:=id.Value, LookIn:=xlValues, lookat:=xlWhole)
    On Error Resume Next
    cg = Cells(source_id.Row, 5).Value
    On Error Resume Next
    cat = Cells(source_id.Row, 6).Value
    target_sheet.Activate
    Cells(id.Row, 11).Value = cg
    Cells(id.Row, 12).Value = cat
    Set id = id.Offset(1, 0)
Loop

End Sub

Before running the macro, make sure that the format of ID column on both sheets are same. Will suggest you to First Clean & Trim the ID Column. Because it is visible in the image that ID column in target sheet has unrecognized characters.

Collectives™ on Stack Overflow

VBA Problems with Concatenating String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related