How to get Col and Row returned in an hLookup?

R

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
 
D

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
 
R

rick

Thank you VERY much Dave.
You are good!

Rick
Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top