Macro continues for thousands of rows instead of stopping

[tlc_in_OK]

I created a macro that puts a formula into I2 of a spreadsheet, and fills it down until the last non-empty row, then it should stop. I had it working, but had to make a couple of adjustments, now it fills down to 1M+ rows. I've made adjustments for hours and can't seem to figure out where my syntax is off. . .

Sub CopyFunctionI()
Range("I2").Formula = "=IF(TYPE($I1)=2,$H2,($H2+$I1))"
Range("I2").AutoFill Destination:=Range("$I$2:$I$" & Cells(Rows.Count, 6).End(xlDown).Row)
End Sub

I tried using ".End(xlUp)" which is how I had it working originally, but it keeps trying to put the first entry into I1, which is where the column header is, and since the formula is looking for a previous cell's value, it errors.

I also am trying to do this same type of function for column J, that takes the value in column I and divides by the sum of all values in column H (a running % of total). But I haven't been successful getting the formula to divide by the sum when I use a range. Any ideas there, too?

Can someone help please.

 

[Desu Nota from Columbus]

Sub CopyFunctionI()
Dim r As Long
Dim lrow As Long

lrow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lrow
Range("I" & r).FormulaR1C1 = "=IF(TYPE(R[-1]C9)=2,RC8,(RC8+R[-1]C9))"
Next r

End Sub

This will fill down I, for as many rows of data in column A. Say last record in A is row 100, then this macro will end with I100.

Change lrow = Range("A" & Rows.Count).End(xlUp).Row if you wish to base this off a different column

 

[Jonmo1]

When using rows.count, you always want to go UP.
Because Rows.Count = the Last useable row in the sheet.
So there is nothing further down, therefore it will result in the last useable row
in XL2003, that is 65536, over 1 million in 2007 or higher..


Now, if you use xlUp, and it goes to 1, that means you've referenced a blank column.
In your example, you're in column 6 (F)
Make sure you're referencing a column that has data in it.

Since the formula you are entering refers to column H, we can assume that column H has data in it, so use 8 instead of 6 in the Cells property

Range("I2").AutoFill Destination:=Range("$I$2:$I$" & Cells(Rows.Count, 8).End(xlUp).Row)


Hope that helps.

 

[Desu Nota from Columbus]

I concur with jonmo1, I wasn't sure why you had Column 6 instead of 8 so I just remade your macro.

here is the finished product with column J included:

Sub CopyFunctionI()
Dim r As Long
Dim lrow As Long
Dim i As Long


lrow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lrow
Range("I" & r).FormulaR1C1 = "=IF(TYPE(R[-1]C9)=2,RC8,(RC8+R[-1]C9))"
Next r

lrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lrow
Range("J" & i).FormulaR1C1 = "=RC[-1]/SUM(R1C[-2]:R59998C[-2])"
Next i

End Sub

 

[Jonmo1]

Sub CopyFunctionI()
Dim r As Long
Dim lrow As Long
 
lrow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lrow
Range("I" & r).FormulaR1C1 = "=IF(TYPE(R[-1]C9)=2,RC8,(RC8+R[-1]C9))"
Next r
 
End Sub

This will fill down I, for as many rows of data in column A. Say last record in A is row 100, then this macro will end with I100.

Change lrow = Range("A" & Rows.Count).End(xlUp).Row if you wish to base this off a different column

Good use of lrow..

But instead of looping, and etering 1 formula at a time...
You can enter the formula in the entire range with one stroke.
The row #s will incriment automatically as they would when filled...

Try
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("I2:I" & lrow).Formula = "=IF(TYPE($I1)=2,$H2,($H2+$I1))"

 

[tlc_in_OK]

Thank you both so much!!! And especially the extra explanation. . .that explains a lot.

Can you answer one more possibly? I'm not sure how to get around having quotation marks in my formula on this one. . .

Sub CopyFunctionK()
Range("K2").Formula = "=IF($G2="Accepted",$F2,"")"
Range("K2").AutoFill Destination:=Range("K2:K" & Cells(Rows.Count, 12).End(xlUp).Row)
End Sub

The first set of quotation marks in my formula, I believe, is acting like the end of the quoted formula and it's erroring. How do I delimit the quotes inside?

 

[Desu Nota from Columbus]

Good use of lrow..

Try
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("I2:I" & lrow).Formula = "=IF(TYPE($I1)=2,$H2,($H2+$I1))"

I always wondered how to avoid the RC notation. Thanks for the advice!

 

[Jonmo1]

When putting a formula in a cell with VBA, and that formula contains quotes..

You just double up the quotes...

Range("K2").Formula = "=IF($G2=""Accepted"",$F2,"""")"

Thank you both so much!!! And especially the extra explanation. . .that explains a lot.

Can you answer one more possibly? I'm not sure how to get around having quotation marks in my formula on this one. . .

Sub CopyFunctionK()
Range("K2").Formula = "=IF($G2="Accepted",$F2,"")"
Range("K2").AutoFill Destination:=Range("K2:K" & Cells(Rows.Count, 12).End(xlUp).Row)
End Sub

The first set of quotation marks in my formula, I believe, is acting like the end of the quoted formula and it's erroring. How do I delimit the quotes inside?

 

[tlc_in_OK]

Perfect! You guys are the best! Thank you.