Using a Cell Address as part of a Lookup formula

[stirlingmw]

Morning All
I am trying to put together a "Help" button that describes what data should in added to certain cells.

What I have is a worksheet "Help" that in column A has a list of all of the cell addresses from my "Data" worksheet that have an input function. I.e. cell $K6$ = data input of Date and therefore Column A in Help has $K6$ and Column B has an explanation "Input a Date".

I can get the lookup function to work if an using a Text string to locate the cell address in Column A, but I want to be able to use ActiveCell.Address to locate the required text. So if I selected cell $K$6 in the "data" worksheet I want the lookup to search column A for the text $K$6 and return what is in Column B.

If I substitute ACell for the text "$K$6" it works.

Sub LookUpHelp()
    Dim sRes As String
    Dim ACell As String
    
    ACell = ActiveCell.address
    sRes = Application.VLookup(ACell, Worksheets("Help").Range("A1:B10"), 2)
    
    MsgBox sRes
    
End Sub

Any ideas?

TIA

Steve

 

[Yongle]

VBA is trying to match the value in cell K6

Amend one line

ACell = "" & ActiveCell.Address & ""

 

[stirlingmw]

VBA is trying to match the value in cell K6

Amend one line

ACell = "" & ActiveCell.Address & ""

Perfect, Thank you.

Steve