0

I have a sheet ("Sheet1") into which I have pulled some data into column F from another sheet. I now need to pull some data into columns A-E using index-match of the new data in Column F.

I identify the first empty row of column A using "emptyrow", and the last empty row using "lastrow". I am trying to use these to autofill the formula below between the first empty cell in A and the last empty cell in E where there is still corresponding data in F.

I'm getting a syntax error on the Autofill and I can't figure out how to make this work. Please help.

Dim lastrow As Long
lastrow = Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row

Dim emptyrow As Long
emptyrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
Range("A" & Emptyrow).FormulaR1C1 = _
"=INDEX(INDIRECT('Control Panel'!R35C3),MATCH(RC6,INDIRECT('Control Panel'!R36C3),0),MATCH(R1C,INDIRECT('Control Panel'!R37C3),0))"
Range("A" & Emptyrow, "E" & "Emptyrow").AutoFill destination:=Range("A" & lastrow, "E" & "lastrow")
vjr2109
  • 43
  • 6
  • `"E" & "Emptyrow"`... variables like `Emptyrow` don't belong in quotes. Same goes for `"E" & "lastrow"`. – BigBen Sep 26 '22 at 19:55
  • Sorry about that, I think I was playing around with it in the editor here. My actual code does not have "" around the variable. Still getting "Method range of object global failed" – vjr2109 Sep 26 '22 at 20:03
  • Maybe you should create a [reprex]. Also, the `Destination` parameter of `.AutoFill` must include the cell you're autofilling from. See the [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofill). – BigBen Sep 26 '22 at 20:03

0 Answers0