0

My VBA macro was created to convert several lines of data into a different format. I do this by using various formulas throughout the macro. Typically, the original file that is copied/pasted into the workbook has more than one line of data. However, my test file only has one line of data.

I get a

Run-time error '1004'

when my code hits the Range.Autofill code shown below.

Historically, I have successfully used the On Error Resume Next function to skip the lines of code like this. But, this isn't working for me on this macro for some reason.

Any ideas why? Should I try a different approach, such as a GoTo function instead?

Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C[10],MATCH(RC[-5],ClientReport!C[-1],0))"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(RawBillingDump!C[38],MATCH(Template!RC[1],RawBillingDump!C[67],0))"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C,MATCH(Template!RC[-1],ClientReport!C[9],0))"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C[-1],MATCH(Template!RC[-3],ClientReport!C[7],0))"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C[-1],MATCH(Template!RC[-4],ClientReport!C[6],0))"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C[-1],MATCH(Template!RC[-5],ClientReport!C[5],0))"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(ClientReport!C[-10],MATCH(Template!RC[-6],ClientReport!C[4],0))"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=INDEX(RawBillingDump!C[38],MATCH(RC[-7],RawBillingDump!C72,0))"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(Stage!RC[3],5)"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=IF(Stage!RC[6]=0,"""",Stage!RC[6])"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=IF(Stage!RC[6]=0,"""",Stage!RC[6])"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=INDEX('RawBillingDump'!C[3],MATCH(RC[-6],'RawBillingDump'!C,0))"
    
    On Error Resume Next
    
'Code snags here, even with On Error Resume Next
   

     Range("B2").AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("C2").AutoFill Destination:=Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("E2").AutoFill Destination:=Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("I2").AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("J2").AutoFill Destination:=Range("J2:J" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("K2").AutoFill Destination:=Range("K2:K" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("L2").AutoFill Destination:=Range("L2:L" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("m2").AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("R2").AutoFill Destination:=Range("R2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("S2").AutoFill Destination:=Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("T2").AutoFill Destination:=Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row)
        Range("X2").AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, "A").End(xlUp).Row)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
colette
  • 15
  • 6
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • And make sure every `Range`, `Cells` and `Rows` object has a worksheet specified like: `ThisWorkbook.Worksheets("Sheet1").Range` • Also remove the `On Error Resume Next` without error handling this line is evil because you cannot see your errors. Checkout [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling). – Pᴇʜ Nov 02 '21 at 07:26
  • Hi Peh, Thanks so much for your response. I looked at How to avoid using Select. I need to look into Ranges more. As for the On Error Resume Next statement, in this particular case it makes sense for what I'm doing here. Usually it works to skip to the next line of code, but in this instance it isn't working and I'm not sure why. – colette Nov 02 '21 at 15:48
  • No a `On Error Resume Next` without error handling is never a good idea. It is like drinking 2 bottles of Wodka and throwing in pain killers instead of drinking less. Fight the cause of your issues not the symptoms. • Make sure every `Range`, `Cells` and `Rows` object has a worksheet specified as I said in my first comment, as that is very likely a cause of your problems. If you still run into issues plase update your code in the question. – Pᴇʜ Nov 02 '21 at 15:54

0 Answers0