Convert Excel Formula to VBA

[Guest]

Dear experts,

My active sheet is P1, from which I have found an id value. I now wish to
'get' a value (let's call it maxpt) which is on another sheet (Summary). If I
were doing this in an Excel formula, the cell would contain the following:

=IF(ISNA(MATCH(D6,Summary!D290,0)),"",OFFSET(Summary!D2,MATCH(D6,Summary!D290,0)-1,1))

where D6 is the id value and the result would be maxpt. How, if at all, does
this convert to VBA? And can I use a VBA variable in place of the 'D6'?

Thanks!

 

[Tom Ogilvy]

Sub AC()
Dim vD6 As Variant, rng As Range, rng1 As Range
vD6 = Worksheets("P1").Range("D6").Value
Set rng = Worksheets("Summary").Range("D290")
res = Application.Match(vD6, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res, 2)
application.Goto rng1, True
Else
MsgBox "Not found"
End If

End Sub

Obviously adjust the assignment of the variable vD6 to whatever you need.