Excel VBA Autofill trouble

Question

I want to autofill a formula down to the last row. If I only choose one column it works fine but if I select a larger range containing both rows and columns the function doesn't fill the first row or the first column. Anyone know why?

The first code snippet works like it is supposed to:

If Not Intersect(Target, Range("V1")) Is Nothing Then
    wsPlan.Range("V4").Formula = "=IFERROR(INDEX(Data!$I:$I,MATCH(VALUE(ROW()&V$2),Data!$A:$A,0)),"""")"
    Range("V4").AutoFill Destination:=Range("V4:V" & Range("E" & Rows.Count).End(xlUp).Row)
End If

The second omits column V and row 4, any ideas why?

If Not Intersect(Target, Range("V1")) Is Nothing Then
    wsPlan.Range("V4").Formula = "=IFERROR(INDEX(Data!$I:$I,MATCH(VALUE(ROW()&V$2),Data!$A:$A,0)),"""")"
    Range("V4").AutoFill Destination:=Range("V4:X" & Range("E" & Rows.Count).End(xlUp).Row)
End If

Where is this code? Module or worksheet code sheet? why do you specify wsPlan in one line and not the other? — user4039065
The code is in the worksheet so it refers to the same cells. I will update them so both are the same in the code, thanks. — Patrick Bender
Using the AutoFill it works as if you're using the corner of the cell in Excel to fill, and doesn't like two way fill. It's columns or rows and not both combined unfortunately. — Clannagh

Unknown Unknown · Accepted Answer · 2018-09-13T13:59:09

Write all of the formulas at once. AutoFill doesn't handle a two direction fill very well.

If Not Intersect(Target, Range("V1")) Is Nothing Then
    with wsPlan
        'write all of the formulas into V:X
        .Range(.cells(4, "V"), .cells(.rows.count, "E").end(xlup).offset(0, 19)).Formula = _
            "=IFERROR(INDEX(Data!$I:$I, MATCH(VALUE(ROW()&V$2), Data!$A:$A, 0)), text(,))"
    end with
End If

Excel VBA Autofill trouble

1 Answers