Formulas using a moving range producing an error message (#NAME?)

[Alroj]

HI intelligent people,

I am trying to set up a macro that contains a formula in cell G7. This formula includes a range with the relevant data that shows up in a different column each month. The way I am thinking is that I search for the title of the data and then select the 300 rows below this cell,. This range is then SET as ABC (Dim as range). Then I use ABC and include it in the formula below BUT no luck in getting any results so far. The problem I have with this formula is that the results I get in cell "G7" is #NAME? and not the number I am trying to fetch.

Your assistance would be greatly appreciated please.

Sub Macro5()
'
Dim ABC As Range

Range("a1:az8").Select
  Selection.Find(What:="XYZ", LookIn:=xlValues).Offset(1, 0).Select
   
   Set ABC = Range(Selection, Selection.Offset(300, 0))

Range("G7").select
 ActiveCell.Formula2R1C1 = "=INDEX(ABC,(@R7C1:R300C1,R7C16:R300C16,FALSE))"

end sub

 

[MARK858]

Not knowing what your manual formula looks like, does the below get you closer

Sub Macro5()
'
Dim ABC As Range

Range("a1:az8").Find(What:="XYZ", LookIn:=xlValues).Offset(1, 0).Select
   
   Set ABC = Range(Selection, Selection.Offset(300, 0))

Range("G7").Formula2R1C1 = "=INDEX(" & ABC.Address(ReferenceStyle:=xlR1C1) & ",@R7C1:R300C1,R7C16:R300C16,FALSE)"

End Sub

 

[Alroj]

Hi MARK858,

Thank you for sharing your wisdom. It worked very well!!

I applied the change you suggested using INDEX/MATCH and got the number I am trying to fetch

=INDEX(" & ABC.Address(ReferenceStyle:=xlR1C1) & ",MATCH(@R7C1:R300C1, R7C16:R300C16,FALSE))

Much appreciated!!

 

[MARK858]

You're welcome

Btw, it is helpful if you ask a question like this in a future if you provide a working version of the formula typed manually in the cell with a working range where the variable would go... in this case something like the below for G7

=INDEX($B$7:$B$300,MATCH(@$A$7:$A$300, $P$7:$P$300,FALSE))

as it means we can test the code we post

 

[Alroj]

Will do. Thank you, Mark858,