# FInd final occurence

Hi,

On Sheet1, column B I have a list of numbers, on sheet 2 I have these numbers at various rows sometimes repeated more than once from row 1 to 100000.  I need to find the row with the last occurrence, using VBA how do I scan in the numbers from column B in sheet 1 and find the last occurrence of each of these numbers on sheet 2??
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
You may try something like this...

``````Sub GetLastRowNumberOfNumbers()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr1 As Long, lr2 As Long, i As Long, ii As Long
Dim x, y, z()

Application.ScreenUpdating = False

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lr1 = ws1.Cells(Rows.Count, "B").End(xlUp).Row  'Assumes that the numbers on Sheet1 are in column B
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row  'Assumes that the numbers on Sheet2 are in column A

x = ws1.Range("B2:B" & lr1).Value   'Assumes that the numbers on Sheet1 are in column B
y = ws2.Range("A1:A" & lr2).Value   'Assumes that the numbers on Sheet2 are in column A

ReDim z(1 To UBound(x, 1), 1 To 1)
For i = 1 To UBound(x, 1)
For ii = UBound(y, 1) To 1 Step -1
If y(ii, 1) = x(i, 1) Then
z(i, 1) = ii
Exit For
End If
Next ii
Next i
'Writing the output in column C on Sheet1
ws1.Range("C2").Resize(UBound(x, 1), 1).Clear
ws1.Range("C2").Resize(UBound(x, 1), 1).Value = z
Application.ScreenUpdating = True
End Sub
``````
0

Experts Exchange Solution brought to you by