How to get Col and Row returned in an hLookup?

[rick]

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]

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]

Thank you VERY much Dave.
You are good!

Rick

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