Autofill in VBA

[Azar]

I know this is fairly common, but my internet searches have not helped me solve the problem.

I have a formula in "E1" and I want my VBA macro to drag or autofill the formula down to the corresponding last cell that has data in Column D.
I tried this:

Range("E1").Select

Selection.AutoFill Destination:=Range("E1:E" & Range("D" & Rows.Count).End(x1UP).Row)

Alas, I get the venerable "run-time error 1004 application defined or object defined error"

any help would be great!

thanks much

 

[Jonmo1]

I know this is fairly common, but my internet searches have not helped me solve the problem.

I have a formula in "E1" and I want my VBA macro to drag or autofill the formula down to the corresponding last cell that has data in Column D.
I tried this:

Range("E1").Select

Selection.AutoFill Destination:=Range("E1:E" & Range("D" & Rows.Count).End(x1UP).Row)

Alas, I get the venerable "run-time error 1004 application defined or object defined error"

any help would be great!

thanks much

That should be
xlUp (as in Excel Up)
It's the LETTER L, not the number one.

Hope that helps.

 

[VoG]

That should be l not 1

Selection.AutoFill Destination:=Range("E1:E" & Range("D" & Rows.Count).End(xlUp).Row)

 

[Azar]

thanks guys! that worked!!

 

[smg]

Hi guys,

I have a similar problem. I've been trying to use either of the following formulas in immediate window:

Range("A2").Copy
Range("B" & Rows.Count).End(xlUp).Offset(0, -1).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).PasteSpecial xlPasteFormulas

or

BLR = Range("B" & Rows.Count).End(xlUp).Offset(0, -1).Row
Range("A2").Select
Selection.autofill Destination:=Range("A2:A" & BLR), Type:=xlFillDefault


----

The macro first inserts a column in "A", then inserts a formula in A2 which I want to copy from A3 to the last row. BLR is the last row which is needed to be taken from column B because A is empty and consequently it will choose the last empty row in the sheet.

I have encountered this problem before. I would appreciate your answer and any advices for future how to deal with it.

Cheers,
Slawek

 

[VoG]

This should work

BLR = Range("B" & Rows.Count).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & BLR), Type:=xlFillDefault

 

[smg]

Thanks for your reply VoG.

I keep getting the same error - 1004.

Do you have any other ideas?

 

[VoG]

That works for me. Check what the value of BLR is.

 

[Andrew Poulsom]

This worked for me in the immediate window:

Range("A2").AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

as did VOG's code.

 

[smg]

Ok, so I have tested your code VoG and I had a problem with MsgBox showing the number of the last row (blank window). I thought there is something wrong with the immediate window.

I incorporated the changes in the macro and run it from the beginning to the end without any errors. The column is appropriately and fully filled with the formulas. In short, it works!

Thanks guys for your help.


PS: Do you know any possible reasons why the immediate window didn't work appropriately? Even after restarting Excel it didn't work correctly.