Fill down Formulas

I'm trying to automatically fill down my formulas in a macro but it doesn't seem to be working.

My values start in row B9 and don't always go down to a certain row # so I want to be able to copy down the formulas in columns E9:T9 until it finds no more data in column B.

What's wrong with my code?
Dim LR As Integer
     LR = Range("B9" & Rows.Count).End(xlUp).Row
     Range("E9").AutoFill Destination:=Range("E9:T" & LR), Type:=xlFillDefault

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
Declare LR as Long in line 1, and in line 2 remove the '9'.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Nope still not working. I'm now getting a "AutoFill method of Range class failed".
0
 
Rory ArchibaldConnect With a Mentor Commented:
Try:

Range("E9:T9").AutoFill Destination:=Range("E9:T" & LR), Type:=xlFillDefault
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Still not working. Now it's deleting all my formulas in row E9:T9 and not copying anything down.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Does it matter that my headers are in row 8 with nothing in rows 1:7?
0
 
StephenJRCommented:
Can you step through the code and check the value of LR? It sounds like you don't have any data.
0
 
Rory ArchibaldCommented:
Code should be:

Dim LR As Long
     LR = Range("B" & Rows.Count).End(xlUp).Row
     Range("E9:T9").AutoFill Destination:=Range("E9:T" & LR), Type:=xlFillDefault

Open in new window


note that I removed the 9 from line 2.
0
 
Rory ArchibaldCommented:
The code also assumes you have data in column B for all the applicable rows.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Got it working. I was running the code before I had any data in column B. Working fine now. Thank you both for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.