How to get Col and Row returned in an hLookup?
Thank you VERY much Dave.
You are good!
Rick
"Dave Peterson" wrote in message
...
I think I'd use application.match(). It'll give an index into that range.
Dim i As Long
Dim res As Variant
Dim myRng As Range
With Worksheets("somesheetnamehere")
Set myRng = .Range("Project_BudgetAllocation_FullRange")
End With
i = 10
res = Application.Match(i, myRng.Rows(1), 0)
If IsError(res) Then
MsgBox i & " wasn't found!"
Else
MsgBox myRng.Rows(2).Cells(1).Offset(0, res - 1).Address
'or
MsgBox myRng(2, res).Address
'or
MsgBox myRng.Cells(1).Offset(2 - 1, res - 1).Address
End If
rick wrote:
I have a table with the Period horizontally across the first row and then
8
lines of Budget items below the Period.
I can do an hLookup and it returns the correct column but it is showing
the
data, (Budget) not the R1C1.
i.e varGroupStart = Application.WorksheetFunction.HLookup(i,
Range("Project_BudgetAllocation_FullRange"), 2, False)
When I look at varGroupStart it shows "279,186".
How do I get it to tell me it is H20?
I need this because then I do an offset :
varGroupStart.Select
varGroupEnd = ActiveCell.Offset(8, i)
in order to select and copy the values.
Thanks for your advice.
Rick
--
Dave Peterson
|