1

I have a macro where it autofills based on the last active row of Column C to fill 2020 in Column A and GM BCR in Column B. However I am getting an "Autofill range of class failed" error when the data only has one row. I tried to write an If statement but it said object required.

   'Drag down 2020 & GM BCR'
    Range("B" & ActiveCell.Row).Select
    ActiveCell.FormulaR1C1 = "GM BCR"
    Dim lastRow As Long
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "C").End(xlUp).Row, "B"))
    Range("A" & ActiveCell.Row).Select
    ActiveCell.FormulaR1C1 = "2020"
    Dim lastRow1 As Long
    lastRow1 = Range("C" & Rows.Count).End(xlUp).Row
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "C").End(xlUp).Row, "A"))

I know my code is probably not optimal but that was the best I could do while tight on time unfortunately. Does anyone know how to fix it so that if I have only one line of data it could still input a single line?

meimei26
  • 11
  • 2
  • 1
    Just check the value of `lastRow`. `If lastRow>1 then ...`. And learn to avoid using activecell. – SJR Jul 20 '20 at 19:14

1 Answers1

0

This should do the trick. It might be worthwhile to look into avoiding the use of '.Select' since this can cause quite a few issues overall.

Sub FillAandB()
Dim ws As Worksheet
Dim rngA As Range, rngB As Range
Dim lastRowC As Long

'use error handling
On Error GoTo err_hand

Set ws = ActiveSheet

'get last row of column C (i.e. #3)
lastRowC = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

'set range A and B for all cells in those columns up to the last row of C
Set rngA = ws.Range(ws.Cells(1, 1), ws.Cells(lastRowC, 1))
Set rngB = ws.Range(ws.Cells(1, 2), ws.Cells(lastRowC, 2))

'turn screenupdating / calculation off for speed
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'fill in ranges
rngA.Formula = 2020
rngB.Formula = "GM BCR"

'turn screenupdating / calculation back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Exit Sub

err_hand:
    
    'turn screenupdating / calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    'display error message
    MsgBox err.Number & " - " & err.Description, vbCritical, "Error"
    
    Exit Sub

End Sub
barvobot
  • 887
  • 1
  • 7
  • 17
  • I'm guessing that `rngA.Formula = 2020` doesn't do what OP is looking for. I understood that the years should be autofilled. We need clarification from OP. Note that it's better to use `.Value` here instead of `.Formula`. – BigBen Jul 20 '20 at 19:17
  • also, in OP question, he is using a string value "2020" not a number data type. We do need a bit of clarification from OP to be able to answer this question. Not clear why `AutoFill` is being used here. – ArcherBird Jul 20 '20 at 19:20
  • My thinking was that setting "2020" as a string meant OP _didn't_ want a bona fide autofill (e.g. 2020, 2021, 2022, etc.), but rather a static 2020 in each cell, but I may be mistaken. I will await OP's response before making any adjustments. @BigBen, I have never encountered a functional difference between `.Formula` and `.Value`, can you elaborate on your thinking? – barvobot Jul 20 '20 at 19:34
  • You're not actually writing a `Formula`, you're writing a `Value`. Best to be explicit. – BigBen Jul 20 '20 at 19:36