findnext

[fraufreda]

Hi.
I'm trying to lookup each value of table1 in table2 giving the second column. In table2 data is sorted.

TABLE1...

LOOKUP VALUE	RESULT
A	A1
A	A2
A	A3
A	A4
B	B1
B	B2
B	B3
C	C1
B	B1
B	B2
B	B3

TABLE2..

COL1	COL2
A	A1
A	A2
A	A3
A	A4
B	B1
B	B2
B	B3
C	C1

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.

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

 

[oldbrewer]

A	A1		1
A	A2		2
A	A3		3
A	A4		4
B	B1		1
B	B2		2
B	B3		3
C	C1		1
B	B1		1
B	B2		2
B	B3		3
COL1	COL2
A	A1			I used a helper column to count same letter sequences
A	A2
A	A3			then this macro populated B1:B11
A	A4
B	B1			Sub Macro4()
B	B2			'
B	B3			' Macro4 Macro
C	C1			' Macro recorded 02/10/2017 by bob
				'
				'
				For j = 1 To 11
				letter = Cells(j, 1)
				numnum = Cells(j, 4)
				For z = 16 To 23
				If Cells(z, 1) = letter Then GoTo 50 Else GoTo 100
				50 mynum = mynum + 1
				If mynum = numnum Then Cells(j, 2) = Cells(z, 2): GoTo 200
				100 Next z
				200 mynum = 0
				Next j
				End Sub

<colgroup><col span="2"><col span="10"></colgroup><tbody>
</tbody>

 

[fraufreda]

Thank you but would it be possible without adding helper column??

 

[oldbrewer]

yes I suppose, I would put some coding in the front of the macro to produce an array with the helper column numbers in it.

maybe
dim numnum (20)
for y= 1 to 11
numnum=right(cells(y,2),1)
next y

and If mynum = numnum(J) then....

delete row 3 of course