Locate the source cell of a LOOKUP function using VBA

[zephan.trent]

I want to locate the cell that a lookup function (VLOOKUP, HLOOKUP, or INDEX) gets its value from.


For example:

if Cell A2 = "lookup_ref"

then VLOOKUP("lookup_ref",A1:D4,3,False) should return the value of cell C2.


I need to be able to find out what the source cell (i.e. C2 in this case is) is for the lookup functions. I want to do this in VBA.

Using excels find precedents doesn't work for this as it returns the ranges rather than the actual cell address that is looked up.

Does anyone know how I can do this?

Thanks very much.

Zeph

 

[Jonmo1]

Welcome to the board...

In a cell formula it would be
="C" & MATCH("lookup_ref",A1:A4,0)

Now, the important question is...

Why do you want the address of the resulting Cell?
There may (probably will) be an easier way to do what you're trying to do..

 

[zephan.trent]

Hi jonmo,

Thanks for your quick reply.

What i'm trying to do is find the precedent cell of a lookup function. I.e. I have a model and I want to know where in a large table of data I am looking up a value from. I'm trying to write a macro that will produce a message box which says:

msgbox "this function looks up cell: " & lookup_source

where lookup_source is the cell that the lookup function got it's data from.

Does that make it clearer?

 

[Jonmo1]

OK, so you're building a sort of "Excel Tutorial" ?
That's cool..

As far as the resulting cell of a vlookup(or any lookup formula), there's no built in function to get the resulting cell's address. You have to be clever..

You can take the leftmost column # of the range, and add the Colref(-1), that equals column # the resulting cell is in.

You can than use
Application.Match(lookupvalue,Columns(column#),0)
to get the row it was found.

Then use
Cells(Row#,Column#).Address to return the Address.


Does that help?

 

[zephan.trent]

Thanks, I think that could work.

The problem now is, how do I extract the column reference from the formula. Say I have:

=VLOOKUP(lookup_ref, range, col_num, 0)

Is there a way in VBA to identify the range and the first column of the range, without having to resort to long and messy text functions?

Thanks

 

[Jonmo1]

If you're working with just basic formulas, like for example..

=VLOOKUP(B1,C:R,5,FALSE)
And say that formula is in A1

This will work. But it is specific to a vlookup formula. It should give you ideas though and will require tweaking for different types of formulas.

Sub test()
Dim myarray As Variant, LookupCol As Long, ColRef As Long, LookupValue As String
Dim ResultCol As Long, ResultRow As Variant
 
'Split the formula into parts using a comma as deliminator
myarray = Split(Range("A1").Formula, ",")
 
'finds the left column of range
LookupCol = Range(myarray(1)).Column
 
'finds the colref
ColRef = myarray(2)
 
'finds the lookup value
LookupValue = Right(myarray(0), Len(myarray(0)) - InStr(1, myarray(0), "("))
 
'calculates the resulting column #
ResultCol = ColRef + LookupCol - 1
 
'calculates the resulting row #
ResultRow = Application.Match(Range(LookupValue), Columns(LookupCol), 0)
 
'Test if the value was found
If IsError(ResultRow) Then
    MsgBox "Value Not Found"
Else
    'combines them into an address
    MsgBox Cells(ResultRow, ResultCol).Address
End If
End Sub