VBA Range.FillRight method not working correctly?

Question

When using the VBA Range.FillRight method, the following cell's formula is correctly copied over as one would expect from using the Fill Handle manually:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(P3,ROW(P3:P80)-ROW(P3),)))*(P3:P80="YES"))

Copies as:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(Q3,ROW(Q3:Q80)-ROW(Q3),)))*(Q3:Q80="YES"))

However,

=IF((SUBTOTAL(103, Tracker[[#Data],[emptyTitle]]))=0,"N/A",(Tracker[[#Totals],[emptyTitle]]/SUBTOTAL(103, Tracker[[#Data],[emptyTitle]])))

Copies as:

=IF((SUBTOTAL(103, Tracker[[#Data],[emptyTitle]]))=0,"N/A",(Tracker[[#Totals],[emptyTitle]]/SUBTOTAL(103, Tracker[[#Data],[emptyTitle]])))

When it should copy as:

=IF((SUBTOTAL(103, Tracker[[#Data],[exName]]))=0,"N/A",(Tracker[[#Totals],[exName]]/SUBTOTAL(103, Tracker[[#Data],[exName]])))

Note: If I manually use the Fill Handle, it works as expected.

Full VBA script:

Sub ADDEX()

Dim ws1 As Worksheet 

Dim tab1 As Object 

Dim exName As String 

Dim lastColumn As Long 

Set ws1 = Sheets("sheetName") 

Set tab1 = ws1.ListObjects("Tracker") 

exName = Application.InputBox("Input Text Here: ", , , , , , , 2) 

With tab1 

    .ListColumns.Add 

    lastColumn = .ListColumns.Count 

    .HeaderRowRange.Cells(1, .ListColumns.Count).Value = exName 

End With 

ws1.Columns(lastColumn).ColumnWidth = 20 

ws1.Range(Cells(tab1.ListRows.Count + 3, 16), Cells(tab1.ListRows.Count + 3, lastColumn)).FillRight 

**ws1.Range(Cells(tab1.ListRows.Count + 4, 16), Cells(tab1.ListRows.Count + 4, lastColumn)).FillRight** 

ws1.Range(Cells(1, 16).Address, Cells(1, lastColumn).Address).Merge 

End Sub

Answer 1

GENERIC DESK WORKER,

FillRight copies the sourcerange as it is.

I think you need AutoFill instead.

Something like:

sourceRange.AutoFill Destination:=fillRange

Jan

Answer 2

Thank you, that seemed to do the trick.

I made myself a couple procedures to maybe use later:

Public Sub rightFiller(ws1 As Worksheet, vertPos As Long, source As Long, target As Long)

Set fillSource = ws1.Range(Cells(vertPos, source), Cells(vertPos, source)) 

Set fillTarget = ws1.Range(Cells(vertPos, source), Cells(vertPos, target)) 

fillSource.AutoFill Destination:=fillTarget 

End Sub

Public Sub downFiller(ws1 As Worksheet, horzPos As Long, source As Long, target As Long)

Set fillSource = ws1.Range(Cells(source, horzPos), Cells(source, horzPos)) 

Set fillTarget = ws1.Range(Cells(source, horzPos), Cells(target, horzPos)) 

fillSource.AutoFill Destination:=fillTarget 

End Sub

Then just Called those procedures for the 2 rightFills I needed:

Call rightFiller(ws1, tab1.ListRows.Count + 3, 16, lastColumn) 'Copies the totaller cell formula

Call rightFiller(ws1, tab1.ListRows.Count + 4, 16, lastColumn) 'Copies the percentage cell formula

Thanks again