Macro for Vlookup based on a Criteria

[user2021]

Need help to get my code corrected. I need the Column C values onto my "working sheet Column B" based on the string value that is "column B in entity sheet". vlookup value would be the entity ID in column A.

Set sht = ActiveWorkbook.Worksheets("working sheet")
Set sht1 = ActiveWorkbook.Worksheets("entity sheet")

LR = sht.UsedRange.Rows.Count

With sht
For i = 2 To LR
If InStr(sht1.Range("B" & i).Value, "WS") Then
sht.Range("B" & i).Value = (Application.VLookup(.Range("A" & i).Value, Worksheets("entity sheet").Range("A2:C5000"), 3, False))
End If
Next i
End With

EDIT: Fixed VBA code

 

[Zot]

You have referred to wrong sheet sht instead of sht1

Set sht = ActiveWorkbook.Worksheets("working sheet")
Set sht1 = ActiveWorkbook.Worksheets("entity sheet")

LR = sht1.UsedRange.Rows.Count

n = 0
With sht1
    For i = 2 To LR
        If InStr(sht1.Range("B" & i).Value, "WS") Then
            n = n + 1
            sht.Range("B" & n).Value = Application.VLookup(.Range("A" & i).Value, sht1.Range("A2:C5000"), 3, False)
        End If
    Next i
End With

However, your column A after HR00015, you have repeated value HR0006, HR0007, HR0008, HR0009 that makes VLookUp capture the previous values.

 

[user2021]

You have referred to wrong sheet sht instead of sht1

Set sht = ActiveWorkbook.Worksheets("working sheet")
Set sht1 = ActiveWorkbook.Worksheets("entity sheet")

LR = sht1.UsedRange.Rows.Count

n = 0
With sht1
    For i = 2 To LR
        If InStr(sht1.Range("B" & i).Value, "WS") Then
            n = n + 1
            sht.Range("B" & n).Value = Application.VLookup(.Range("A" & i).Value, sht1.Range("A2:C5000"), 3, False)
        End If
    Next i
End With

However, your column A after HR00015, you have repeated value HR0006, HR0007, HR0008, HR0009 that makes VLookUp capture the previous values.

Yes that's the issue i have. The vlookup is picking the first value. But i want the values based on source id (column b). if the string that contains "WS" in source id, i want that value alone to be picked up. this code is not working.

 

[Zot]

Yes that's the issue i have. The vlookup is picking the first value. But i want the values based on source id (column b). if the string that contains "WS" in source id, i want that value alone to be picked up. this code is not working.

When you asked to fix the code, obviously it is not working at all because you are looking up on wrong sheet. That was fixed but I have no idea what you were trying to achieve since it wasn't mentioned. Let us know what is your expected result.

It sounds like you do not need to lookup the column A at all since as long as column B starts with WS, then just transfer column C value to another sheet. Isn't that so?

 

[user2021]

When you asked to fix the code, obviously it is not working at all because you are looking up on wrong sheet. That was fixed but I have no idea what you were trying to achieve since it wasn't mentioned. Let us know what is your expected result.

It sounds like you do not need to lookup the column A at all since as long as column B starts with WS, then just transfer column C value to another sheet. Isn't that so?

 

[user2021]

When you asked to fix the code, obviously it is not working at all because you are looking up on wrong sheet. That was fixed but I have no idea what you were trying to achieve since it wasn't mentioned. Let us know what is your expected result.

It sounds like you do not need to lookup the column A at all since as long as column B starts with WS, then just transfer column C value to another sheet. Isn't thatI have

When you asked to fix the code, obviously it is not working at all because you are looking up on wrong sheet. That was fixed but I have no idea what you were trying to achieve since it wasn't mentioned. Let us know what is your expected result.

It sounds like you do not need to lookup the column A at all since as long as column B starts with WS, then just transfer column C value to another sheet. Isn't that so?

The expected result is put as a table above

 

[Zot]

This should work

Sub LookUpCriteria()

Dim eRow As Long
Dim cell As Range, rngData As Range
Dim Element As Variant, dData  As Object
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("working sheet")
Set ws2 = ActiveWorkbook.Worksheets("entity sheet")

eRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
Set rngData = ws1.Range("A2", "A" & eRow)
Set dData = CreateObject("Scripting.Dictionary")

For Each cell In rngData
    If Left(cell.Offset(0, 1), 2) = "WS" Then
        dData.Add cell.Value2, cell.Offset(0, 2).Value2
    End If
Next

For Each cell In rngData
    For Each Element In dData
        ws2.Range("A" & cell.Row) = cell.Value2
        If cell.Value2 = Element Then
            ws2.Range("B" & cell.Row) = dData(Element)
        End If
    Next
Next

End Sub

 

[user2021]

This should work

Sub LookUpCriteria()

Dim eRow As Long
Dim cell As Range, rngData As Range
Dim Element As Variant, dData  As Object
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("working sheet")
Set ws2 = ActiveWorkbook.Worksheets("entity sheet")

eRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
Set rngData = ws1.Range("A2", "A" & eRow)
Set dData = CreateObject("Scripting.Dictionary")

For Each cell In rngData
    If Left(cell.Offset(0, 1), 2) = "WS" Then
        dData.Add cell.Value2, cell.Offset(0, 2).Value2
    End If
Next

For Each cell In rngData
    For Each Element In dData
        ws2.Range("A" & cell.Row) = cell.Value2
        If cell.Value2 = Element Then
            ws2.Range("B" & cell.Row) = dData(Element)
        End If
    Next
Next

End Sub

This isn't working. The column B in working sheet is blank

 

[Zot]

This isn't working. The column B in working sheet is blank

What column B? I was just following the expected result you were showing.

 

[user2021]

What column B? I was just following the expected result you were showing.

am not getting the expected result. on running the code, the expected result column - "source entity id - wss" is blank.