CODEWITHSUNDEEP
vbaconcatenation
InternInNeed
InternInNeed

Reputation: 159

VBA Problems with Concatenating String

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

enter image description here

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

enter image description here

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

Upvotes: 4

Views: 96

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

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.

Upvotes: 1

Related Questions