VBA Code toCopy Formula Down Column until No more data

[Kacer26]

Hello,

I am looking to copy a formula down a column range, but I only want the formula to be copied until the last cell with data. Essentially, I'm trying to use some type of untilblank() functionality.

I need to do this for about 7 - 8 columns in the worksheet, and each of the formulas are different for each of the columns.

Any assistance would be greatly appreciated.

Here's what I have so far (and I don't want to use this type of coding practice because I know it will slow down the run time of my macro overall):

Range("AJ2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-14]>=38626,RC[-14]<=38990),""Y1"",IF(AND(RC[-14]>=38991,RC[-14]<=39355),""Y2"",IF(AND(RC[-14]>=39356,RC[-14]<=39721),""Y3"",IF(AND(RC[-14]>=39722,RC[-14]<=40086),""Y4"",IF(AND(RC[-14]>=40087,RC[-14]<=40451),""Y5"",IF(AND(RC[-14]>=40452,RC[-14]<=40816),""Y6"",""""))))))"
Range("AJ2").Select
Selection.AutoFill Destination:=Range("AJ2:AJ65536")

 

[texasalynn]

add this to the beginning of your code

Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
and change this
Range("AJ2").Select
Selection.AutoFill Destination:=Range("AJ2:AJ65536") 
to this
Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & LR)

 

[Kacer26]

WOW! Thanks that worked perfectly!

 

[aw020a]

I am dealing with the same problem Kacer26 was having. I have applied the above suggestion which works for the past 4 columns of formulas (BN, BO, BP, and BQ).

However once I get to the next two columns which contain if formulas, my code breaks with a "run-time error'1004' application-defined or object-defined error.

here is the line that is breaking:
Range("BS3").Select
Application.CutCopyMode = False
ActiveCell.Formula = "=IF(BO3<31,AV3,"")"
Range("BS3").AutoFill Destination:=Range("BS3:BS" & LR)


thanks in advance for the help.

 

[texasalynn]

open a new post - rules are that additional people don't piggy-back on an issue. Also include all of your code please in the new post. Let me know what the new post is and I will take a look