Hi.
I'm trying to lookup each value of table1 in table2 giving the second column. In table2 data is sorted.
TABLE1...
[table="width: 500, class: grid, align: left"]
[tr]
[td]LOOKUP VALUE[/td]
[td]RESULT[/td]
[/tr]
[tr]
[td]A[/td]
[td]A1[/td]
[/tr]
[tr]
[td]A[/td]
[td]A2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A3[/td]
[/tr]
[tr]
[td]A[/td]
[td]A4[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[tr]
[td]C[/td]
[td]C1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[/table]
TABLE2..
[table="width: 500, class: grid, align: left"]
[tr]
[td]COL1[/td]
[td]COL2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A1[/td]
[/tr]
[tr]
[td]A[/td]
[td]A2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A3[/td]
[/tr]
[tr]
[td]A[/td]
[td]A4[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[tr]
[td]C[/td]
[td]C1[/td]
[/tr]
[/table]
I TRIED TO USE FINDNEXT TO GIVE ME THE SECOND COL IN THE RESULT BUT IT IS NOT WORKING AND TAKES A LOT OF TIME.
I'm trying to lookup each value of table1 in table2 giving the second column. In table2 data is sorted.
TABLE1...
[table="width: 500, class: grid, align: left"]
[tr]
[td]LOOKUP VALUE[/td]
[td]RESULT[/td]
[/tr]
[tr]
[td]A[/td]
[td]A1[/td]
[/tr]
[tr]
[td]A[/td]
[td]A2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A3[/td]
[/tr]
[tr]
[td]A[/td]
[td]A4[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[tr]
[td]C[/td]
[td]C1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[/table]
TABLE2..
[table="width: 500, class: grid, align: left"]
[tr]
[td]COL1[/td]
[td]COL2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A1[/td]
[/tr]
[tr]
[td]A[/td]
[td]A2[/td]
[/tr]
[tr]
[td]A[/td]
[td]A3[/td]
[/tr]
[tr]
[td]A[/td]
[td]A4[/td]
[/tr]
[tr]
[td]B[/td]
[td]B1[/td]
[/tr]
[tr]
[td]B[/td]
[td]B2[/td]
[/tr]
[tr]
[td]B[/td]
[td]B3[/td]
[/tr]
[tr]
[td]C[/td]
[td]C1[/td]
[/tr]
[/table]
I TRIED TO USE FINDNEXT TO GIVE ME THE SECOND COL IN THE RESULT BUT IT IS NOT WORKING AND TAKES A LOT OF TIME.
Code:
Dim ws As Worksheet
Dim LRB As Long
Dim cell As Range
Dim Orng As Range
Dim iCounter As Integer
Dim LOOKUPRng As String
Set ws = Sheets("BOM")
LRB = Sheets("bom").Cells(Rows.Count, "F").End(xlUp).Row
With Range("Table5[out]")
LOOKUPRng = .Find(ComboBox2.Value, , xlValues).Offset(0, 9).Value & "[LOOKUP VALUE]"
End With
For Each cell In ws.Range("A2:A" & LRB)
With Range(LOOKUPRng)
Set Orng = .Find(cell.Value, , xlValues)
iCounter = 2
Do
If Not Orng Is Nothing Then
ws.Cells(iCounter, 2).Value = Orng.Offset(0, 1).Value
Set Orng = .FindNext(Orng)
End If
Loop While iCounter <= LRB
End With
Next