Convert Excel Formula to VBA
 

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!D2:D90,0)),"",OFFSET(Sum mary!D2,MATCH(D6,Summary!D2:D90,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!

Convert Excel Formula to VBA
 

Sub AC()
Dim vD6 As Variant, rng As Range, rng1 As Range
vD6 = Worksheets("P1").Range("D6").Value
Set rng = Worksheets("Summary").Range("D2:D90")
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.

--
Regards,
Tom Ogilvy




"John V" wrote in message
...
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!D2:D90,0)),"",OFFSET(Sum mary!D2,MATCH(D6,Summary!D
2:D90,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!