Autofill up

[fabling]

I'm fairly new to Excel, so bear please bear with me.

I'm trying to make a macro that autofills up. The range that it needs to fill changes, so I've made a string variable(insStr) that has the cell range needed.

I've recorded a macro while doing this manually and the code is exact the same aside from the variable. Also, my string has the same value as the range that comes up in the recorded macro (I've used a msgbox to double check). The recorded macro works fine when run, yet mine does not.

Error message is:

Run-time error '1004':

AutoFill method of Range class failed


What am I missing here? Are you not able to use a variable for the range?


Here is the code:
Range(insStr).Select
Selection.AutoFill Destination:=Range(insStr), Type:=xlFillDefault
Range(insStr).Select


Thank you,

Ben

 

[texasalynn]

Welcome to MrExcel Board...

so if you manually try to do the same thing what happens? Does the variable make sense? Just something to consider

 

[fabling]

It works when I do it manually and when the macro that I recorded while doing so is run.

Maybe the row values are transposed in the recorded macro(the first row value is greater than the second ex. A3:B2). I'll check that when I get back to my PC next week.

Thank you for the reply.

 

[ukmikeb]

Hi

Try this -

Dim Sourcerng As Range
Set Sourcerng = Range(insStr).Offset(Range(insStr).Rows.Count - 1).Resize(1)
Sourcerng.AutoFill Destination:=Range(insStr), Type:=xlFillDefault
Range(insStr).Select

The cell which you want to Autofill has to be part of the Range, in this case you want the last cell in the Range.

What you're doing is specifying the whole range to autofill itself which is causing the error.

The solution I have provided will use the last cell in the Range irrespective of the size of the Range.

You don't need to select the Range before autofilling, indeed you'll see it stated throughout the board that you rarely, if ever, have to Select a cell while using VBA.

If you specify Range addresses the wrong way round Excel will self-correct.

hth

 

[ukmikeb]

Or this which is a neater approach for setting the Sourcerng -

Dim Sourcerng As Range
Set Sourcerng = Application.WorksheetFunction.Index(Range(insStr), Range(insStr).Rows.Count)
Sourcerng.AutoFill Destination:=Range(insStr), Type:=xlFillDefault
Range(insStr).Select

hth

 

[fabling]

Thank you for clarifying that Mike. Now that I look at it, I can see my error. I appreciate the help.


Ben

 

[ukmikeb]

Ben

Pleased to have helped solve your problem.

Thanks for the feedback.

Good luck with your project.