FInd final occurence


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?
Subodh Tiwari (Neeraj)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

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Answered the original question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.