[VBA] how can a Vlookup return the Formula instead of the Value?

[Richard]

Is it possible, using VBA, to get the FORMULA instead of the VALUE of a
cell using a VLOOKUP ?

When I do this in VBA:
result = Application.VLookup(strName, _
Names("t_Feats").RefersToRange, _
Names("index_column").RefersToRange, False)

'result' contains the value form the Vlookup - but I need the FORMULA
that has generated that value - not the value itsef !!

If it's not possible to do this with VLOOKUP, is there another way [in
VBA] to get the formula from a specific cell in the table range? The
vlookup allows me to easily locate the proper cell in the table...

strName : the NAME of the item that is searched (a la VLOOKUP, 1st
parameter)
t_Feats : Named cell representing the table I need to search
index_column : The column where the required information is located

Thanks for your help!
Richard

 

[Tom Ogilvy]

Dim res as Variant, rng as Range
Dim rng1 as Range
set rng1 = Names("t_Feats").Columns(1).Cells
res = Application.Match(strName, rng1,0)
if not iserror(res) then
set rng = rng1(res)
msgbox rng.offset(0,Names("index_Column") _
.ReferstoRange.Value - 1).Formula
Else
msgbox strName & " not found"
End Sub

assumes the code you showed actually works.
Names("index_column").RefersToRange should refer to a single cell that has
the column number in t_Feats columns which has the formula you want to
return.

 

[Richard]

Exactly what I needed !!
Thank you very very much !!

Richard