Find/replace old hyperlinks with new ones based on lookup table

[jpk89]

A1 on the current sheet is the description of corresponding value in a table on sheet 1.
Somewhere in the current sheet in column b is an old hyperlink.
I would like to replace the old link with a new one linking back to the cell on sheet1 displaying the value found there.

For example:

the title of the current sheet in A1 might be "rapper's savings account"
the lookup would find the corresponding value "$0.50" from I37 on sheet1.
the hyperlink on the current sheet would link back to I37 and display $0.50

Debugger runs to 'Vlocation = .... '
The Error is "Unable to get the match property of the worksheet function"


Here is my code so far

Sub LinkReplaceB()

Dim RNG As Range
Dim CEL As Range
Dim VReturn As Variant
Dim VLocation As Range


Set RNG = Range("b1:b100")



For Each CEL In RNG

If (CEL.Hyperlinks.Count) > 0 Then

VReturn = Application.VLookup("a1", Sheet1.Range("f10, i300"), 3, False)
VLocation = Application.Address(WorksheetFunction.Match("a1", Range("f10,i300"), 0))

CEL.Hyperlinks.ClearContents

ActiveSheet.Hyperlinks.Add Anchor:=CEL, Address:="", SubAddress:= _
"vlocation", TextToDisplay:="vreturn"


Else


End If


Next CEL


End Sub



Thanks,

J