Hi Biff,

Have a look at the code below.

It uses a loop looping backwards through the list to find the last matching
cell in the list.

I've added a little bit of extras for you to consider regarding constants
and setting ranges.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Application.EnableEvents = False 'don't need not changing any data
' use constants to make it easier to change
Const cszRange As String = "A1:A100"
Const cszCell As String = "D1"
' variables
Dim lRow As Long, lRowStart As Long, lrowEnd As Long
Dim test As Variant

' may be a range...
If Not (Intersect(Target, _
Union(Range(cszCell), Range(cszRange))) Is Nothing) Then

'in the area
' calc the start and stop rows ie can change the area
lRowStart = Range(cszRange).Row
lrowEnd = Range(cszRange).Rows.Count + lRowStart - 1
test = Range(cszCell).Value ' test cell
For lRow = lrowEnd To lRowStart Step -1 ' work backwards
If test = Cells(lRow, 1) Then
Cells(lRow, 1).Activate
Exit Sub
End If
Next lRow
Cells(1, 1).Activate
End If
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"T. Valko" wrote:

Hi Folks!

Getting my "feet wet" in some VBA. (it's about time!)

I'm playing around trying to do this based on another post.

I have this formula in cell E1:

=IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1))

It returns a cell address.

Then I have this event macro that uses the result of that formula:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$D$1" Then
Application.Goto
Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 "))
End If
enditall:
Application.EnableEvents = True
End Sub

This works just fine but my question is how can I incorporate the formula
(or maybe some better method) directly into the macro and not have to use
the worksheet formula?

Thanks!
Biff