Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Col and Row returned in an hLookup?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get Col and Row returned in an hLookup?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A value is returned | Excel Worksheet Functions | |||
How to see columns from which values were not returned by HLOOKUP functions? | Excel Worksheet Functions | |||
How to see columns from which values were not returned by HLOOKUP functions? | Excel Programming | |||
#Value! Returned ? | Excel Worksheet Functions | |||
#Ref! Returned | Excel Programming |