4elephants
06-20-2008, 05:03 AM
I have a data set that is 20 columns wide (A:T), but the number rows varies by month. This VBA code is designed to add a new column (in U), with a heading DW @ 5% in U1. In U2 a formula is entered and then autofilled down to the last row. It worked great when it had a fixed range (U2:U2536), but I have modified (at least I tried to) the code so it will cope with various column lengths of data. The problem I have though is I get an error message at the red text. The error message reads ?Run-time error 1004 Method ?Range? of object?_global? failed.
Could someone please take a look at my code and see where I am going wrong?
Any help would be most appreciated.
Dim LastRow As Long
With ActiveWorkbook
Sheets("Main").Select
With .Worksheets("Main")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
End With
Range("U1").Select
ActiveCell.FormulaR1C1 = "DW @ 5%"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=if(rc1=""ZH0069"",0,ROUND(OR(RC15<70960,RC15>70971,RC15=70995)*AND(RC13=""Hire"")*(RC17*5%),2))"
Selection.AutoFill Destination:=Range("Main!R2C21:R" & LastRow & "C21")
Range("Q:R,U:U").Select
Range("U1").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "$#,##0.00"
Columns("U:U").Select
Columns("U:U").Copy
Columns("U:U").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("Instructions").Select
End With
End Sub
Could someone please take a look at my code and see where I am going wrong?
Any help would be most appreciated.
Dim LastRow As Long
With ActiveWorkbook
Sheets("Main").Select
With .Worksheets("Main")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
End With
Range("U1").Select
ActiveCell.FormulaR1C1 = "DW @ 5%"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=if(rc1=""ZH0069"",0,ROUND(OR(RC15<70960,RC15>70971,RC15=70995)*AND(RC13=""Hire"")*(RC17*5%),2))"
Selection.AutoFill Destination:=Range("Main!R2C21:R" & LastRow & "C21")
Range("Q:R,U:U").Select
Range("U1").Activate
Selection.Style = "Currency"
Selection.NumberFormat = "$#,##0.00"
Columns("U:U").Select
Columns("U:U").Copy
Columns("U:U").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("Instructions").Select
End With
End Sub