Yes, it is about time! <G I would probably use the Find Method. AFAIK -
VBA doesn't have much support for array formulae, although I know you can use
the Evaluate method with Sumproduct (you can probably find some good examples
of that in this forum).



Sub Test()
Dim strAddress As String

On Error Resume Next
With Sheets("Sheet1").Range("A1:A100")
strAddress = .Find(what:=Range("D1").Value, _
after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlPrevious, _
MatchCase:=False, matchbyte:=False).Address
End With
On Error GoTo 0

If Len(strAddress) = 0 Then _
strAddress = "A1"

End Sub



"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