CODEWITHSUNDEEP
vbaexcel
Patrick Bender
Patrick Bender

Reputation: 467

Excel VBA Autofill trouble

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

Upvotes: 0

Views: 232

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 3

Related Questions