stirlingmw
Board Regular
- Joined
- Feb 18, 2013
- Messages
- 75
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.
Any ideas?
TIA
Steve
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.
VBA Code:
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