Thanks, Tom. Works just fine.

JMB wrote: I believe he is looking for the *last* occurence of D1 in
A1:A100.

Yes, that is correct.

Thanks to everyone for their input.

Biff

"Tom Ogilvy" wrote in message
...
Its a possibility or he is just using max to pick out the only unique
match.

If he wants the last occurrence:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim res As Variant, rng As Range
If Target.Address = "$D$1" Then
Set rng = Range("A1:A100").Find( _
What:=Target.Value, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If rng Is Nothing Then
Set rng = Cells(1, 1)
End If
Application.Goto _
Reference:=rng
End If

End Sub


--
Regards,
Tom Ogilvy


"JMB" wrote in message
...
I believe he is looking for the *last* occurence of D1 in A1:A100.

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim res as Variant, rng as Range
If Target.Address = "$D$1" Then
res = application.Match(Target,Range("A1:A100"),0)
if iserror( res) then
set rng = cells(1,1)
else
set rng = cells(res,1)
end if
Application.Goto _
Reference:=rng
End If

End Sub

--
Regards,
Tom Ogilvy


"T. Valko" wrote in message
...
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