Re: Convert Excel Formula to VBA

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <jmvenabl@xxxxxxxxxxx> wrote in message
news:D7418480-2D88-44F9-AC18-47FEA59D069F@xxxxxxxxxxxxxxxx
> Dear experts,
>
> My active *** 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 *** (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(Summary!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!


.

Quantcast