Autofill givin an error

Assamita

Board Regular
Joined
Sep 23, 2011
Messages
131
Hi everyone.

I'm using the following code:

Code:
Range("A2").AutoFill Destination:=rango, Type:=xlFillValues
Instead of:

Code:
Range(rango).FillDown

Because I want only to copy the values, not the formatting. But I'm getting the following error:
"AutoFill method of Range class failed"

Any ideas what could be wrong?

Thank you
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
What cell contains the value you want to copy, A2 or rango?

Can you state the cell to be copied and the range you want to copy the values to?
 
Upvote 0
A2 is the cell I want to be copied.
rango is a variable I've set before and it's the destination (it could be something like rango = "A3:A100")

This is the full code now, it isn't working either:

Code:
    lastrow = Range("M" & Rows.Count).End(xlUp).Row
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=INDEX('Backlog Report'!C1:C1,MATCH(RC[12],'Backlog Report'!C13:C13,0))"
    Selection.Copy
    rango = "A3:A" & lastrow
    Range(rango).Select
    Selection.PasteSpecial Type:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
Upvote 0
You're putting a formula into A2. You then say you want A2 to be copied with the values filled down.

For absolute clarity, do you mean:

1) Put formula into A2
2) Copy the (calculated) value into A3:A & lastrow

Or

1) Put formula into A2
2) Copy formula into A3:A & lastrow
3) Hard code the values in A3:A & lastrow

If it's the second, then try:
Code:
lastrow = Range("M" & Rows.Count).End(xlUp).Row
With Range("A2")
   .FormulaR1C1 = "=INDEX('Backlog Report'!C1:C1,MATCH(RC[12],'Backlog Report'!C13:C13,0))"
   .Copy
   With .Resize(lastrow-1)
        .pastespecial xlpasteformulas
        .Calculate
        .Value = .Value
   End With
End With
Application.CutCopyMode = False
However if it's the first, then try:
Code:
lastrow = Range("M" & Rows.Count).End(xlUp).Row
With Range("A2")
    .FormulaR1C1 = "=INDEX('Backlog Report'!C1:C1,MATCH(RC[12],'Backlog Report'!C13:C13,0))"
    .Resize(lastrow - 1).Value = .Value
End With
 
Upvote 0
Yes, sorry, I noticed that later. It's the second one as you said. It seems to work. Thank you very much :)
 
Upvote 0
Just one more quick question.

I'm doing that in several columns of the same sheet, so I'm thinking that I could leave the formulas, and after all are done, copy and paste all the values in the sheet. That way I would save some processing time.

The second part I know how to do it. What would I need to remove so the macro only copies the formulas, but doesn't copy and paste values?

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top