Macro to find last time an ID was used

[Ken]

Hi again Group,
I have been using this sub for a long time and it was written for me
by someone in the groups. It checks to see if an ID was used before
and tells me which line number it is on if it was. The problem is that
now some of the ID's are starting to show up for the 3rd time, and the
sub only checks from the top down and tells me the 1st time it was
used, not the 2nd. How can this be modified to check from the bottom
up? Thanks in advance for any help!
Ken


Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Target.Column <> 8 Or Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Application.CountA(Range(Cells(1, 8), Cells(Target.Row - 1, 8)),
Target.Value) <> 0 Then
On Error GoTo 0
On Error Resume Next
If MsgBox("This PegaSys ID was previously entered for Job Number " & _
Application.Match(Target.Value, Range(Cells(1, 8), Cells(Target.Row -
1, 8)), 0) _
& vbLf & "Do you really wish to continue? ", vbQuestion + vbYesNo,
"ID Found") = vbYes Then
On Error GoTo 0
Exit Sub
Else
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

 

[Mike H]

Maybe this,



Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column <> 8 Or Target.Cells.Count > 1 Or Target.Row = 1 Then Exit
Sub
On Error Resume Next
If WorksheetFunction.CountIf(Range(Cells(1, 8), Cells(Target.Row - 1, 8)),
Target.Value) <> 0 Then
On Error GoTo 0
On Error Resume Next
lastrow = Target.Row - 1
For i = lastrow To 1 Step -1
If Cells(i, 8).Value = Target.Value Then
jobno = Cells(i, 8).Row
Exit For
End If
Next
If MsgBox("This PegaSys ID was previously entered for Job Number " & jobno & _
vbLf & "Do you really wish to continue? ", vbQuestion + vbYesNo, "ID Found")
= vbYes Then
On Error GoTo 0
Exit Sub
Else
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[Ken]

Hi Mike....Thanks so much for your help! Ihad to add the following to
the sub to get it to work, but it works very well....am I correct?

Dim LastRow As Long
Dim i As Long
Dim jobno As Integer

Again, thank you for your time and your help!
Ken

 

[Mike H]

Hi,

I should have dimensioned the extra variables I added. glad I could help.

Mike