1

I am using a macro to append a new column of data at the far right of my data set. The code inserts a title in Row 1 and a Formula in Row 2. I am trying to fill the formula from row 2 down to the end. I am having difficulty with the last column variable in the fill statement.

x = Worksheets("DateTracker").UsedRange.Columns.Count
Worksheets("DateTracker").Cells(1, x + 1) = "Title"
Worksheets("DateTracker").Cells(2, x + 1) = "Formula"


ws.Range(.Cells(2, x + 1) & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

I get an Invalid or unqualified reference error.

Thank you.

user3071992
  • 11
  • 1

2 Answers2

1
  1. UsedRange is unreliable. Use End(xlToLeft) to find the last column.
  2. Formulas with relative references can be written to the entire range, avoiding the use of FillDown.
  3. .Cells(2, x + 1) is the unqualified reference as you're missing a With ... statement that .Cells would refer to.
Dim ws As Worksheet
Set ws = Worksheets("DateTracker")

With ws
    Dim nextColumn As Long
    nextColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1

    .Cells(1, nextColumn).Value = "Title"

    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    .Range(.Cells(2, nextColumn),.Cells(lastRow, nextColumn)).Formula = "Formula"
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Both of these formulas work really well, and I thank you for the help. I really appreciate it.The fist one seems slightly faster. – user3071992 Mar 25 '21 at 19:35
0

Fill Down

Option Explicit

Sub trackFill()
    Dim ws As Worksheet: Set ws = Worksheets("DateTracker")
    Dim x As Long: x = ws.UsedRange.Columns.Count + 1
    ws.Cells(1, x) = "Title"
    ws.Cells(2, x) = "Formula"
    ' About '- 1': LR - FR + 1 = LR - 2 + 1 = LR - 1
    ws.Cells(2, x).Resize(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - 1).FillDown
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you so much I really appreciate your help. – user3071992 Mar 25 '21 at 19:35
  • `Dim x As Long: x = ws.UsedRange.Columns.Count + 1` might produce unexpected results if the first populated column is not A. @user3071992, `UsedRange` is not a reliable way to find the last populated column. See [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for an explanation of its pitfalls. – BigBen Mar 25 '21 at 19:36