Efusu
04-27-2020, 08:58 AM
Hi.
I need to create an Index/Match based on a variable range. I used Find and Address to locate the variable cell address, however now I need to identify the range based on the cell address to insert it in the index match/function. I am new to VBA so takes lots of time to figure out how to do that. Anyone can help?
Here is what I have so far (I am unsure what to do with POS.Address in red below to be able to define the range), as I have only the row/cell from $C$5 to $AD$5 if I use POS/Address and I need it to go to $C$5:$C$70, or $B$5:$B$70 etc up to $AD$5:$AD$70 depending on what is my active sheet name
Sub findPOS()
Dim POS As Range
Dim CustRev As Double
Set POS = Worksheets("WW Financials").Range("C5:AD5").find(what:=ActiveSheet.Name, LookIn:=xlValues)
Worksheets("WW Financials").Activate
CustRev = WorksheetFunction.Index(Range(POS.Address), WorksheetFunction.Match("Customer Revenue", Range("B2:B100"), 0))
End Sub
Thank you!
I need to create an Index/Match based on a variable range. I used Find and Address to locate the variable cell address, however now I need to identify the range based on the cell address to insert it in the index match/function. I am new to VBA so takes lots of time to figure out how to do that. Anyone can help?
Here is what I have so far (I am unsure what to do with POS.Address in red below to be able to define the range), as I have only the row/cell from $C$5 to $AD$5 if I use POS/Address and I need it to go to $C$5:$C$70, or $B$5:$B$70 etc up to $AD$5:$AD$70 depending on what is my active sheet name
Sub findPOS()
Dim POS As Range
Dim CustRev As Double
Set POS = Worksheets("WW Financials").Range("C5:AD5").find(what:=ActiveSheet.Name, LookIn:=xlValues)
Worksheets("WW Financials").Activate
CustRev = WorksheetFunction.Index(Range(POS.Address), WorksheetFunction.Match("Customer Revenue", Range("B2:B100"), 0))
End Sub
Thank you!