PDA

View Full Version : [SOLVED] Variable Columns Lengths



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

marshybid
06-20-2008, 05:10 AM
Hi, try this code below, amended yours (amendment in green);




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))"
ActiveCell.Offset(0, -1).range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).range("A1").Select
ActiveCell.Value = "1"
Selection.End(xlUp).Select
Selection.Copy
range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
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


The amendment code assumes that you have data all the way down the column immediately to the left of U2 (ActiveCell.Offset(0, -1).range("A1").Select)

Try this, it works fine for me.

Marshybid :hi:

4elephants
06-20-2008, 06:11 AM
Marshybid, you are the man, twice this week you've helped me out.

Thanks very much for your help.

:beerchug:

marshybid
06-20-2008, 07:14 AM
Marshybid, you are the man, twice this week you've helped me out.

Thanks very much for your help.

:beerchug:

Glad to have been able to help.

I raise threads on this site regularly and am always provided with fantastic suggestions and solutions. If I'm able to give something back I will.

Marshybid