index/match based on variable range

[Efusu]

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!

[p45cal]

Perhaps something along the lines of:

Sub findPOS()
Dim POS As Range
Dim CustRev As Double

With Worksheets("WW Financials")
  Set POS = .Range("C5:AD5").Find(what:=ActiveSheet.Name, LookIn:=xlValues)
  If Not POS Is Nothing Then
    CustRev = WorksheetFunction.Index(POS.Resize(66), WorksheetFunction.Match("Customer Revenue", .Range("B2:B100"), 0))
  Else
    MsgBox "Couldn't find " & ActiveSheet.Name & " in row 5"
  End If
End With
End Sub

[Efusu]

Thank you! This worked, I just had to change the Range for the match to be same as for Index, as these were not same size ( I had up to row 70 for Index and up to row 100 for Match) and it was distorting a bit.