Feb 19, 2008

I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front.

Here is the code for the columns additions, and formulas:

Columns("O:S").Insert Shift:=xlToRight

Columns("W:Z").Insert Shift:=xlToRight

Columns("AB:AF").Insert Shift:=xlToRight

Columns("AH:Al").Insert Shift:=xlToRight

Range("O2").FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])"

Range("P2").FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))"

Range("Q2").FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))"

Range("R2").FormulaR1C1 = "=RIGHT(RC[-4],4)"

Lets take this first one for example:

Range("O2:S2").Copy Range("O2:S2", Range("O2:S2" & Range("A" & Rows.Count).End(xlUp).Row))

If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254.

How can this code be modified so the formulas will not be copied past the last row of data?

How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.

View 9 Replies
View Related