Storing a range in a variable
 

Hi

I need to save a range in a variable for later use in an autofill function.
It fails on the autofill line as the value in rng is returned as TRUE and
not
the range data I expected.

I have tried different permutations but all that changes is the error
message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

Storing a range in a variable
 

set RNG = Range("A2", Range("A2").End(xlDown))
Rng.Select

--
Regards,
Tom Ogilvy


"Mick" wrote in message
...
Hi

I need to save a range in a variable for later use in an autofill
function.
It fails on the autofill line as the value in rng is returned as TRUE and
not
the range data I expected.

I have tried different permutations but all that changes is the error
message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

Storing a range in a variable
 

CHange
RNG = Range("A2", Range("A2").End(xlDown)).Select
to
Set RNG = Range("A2", Range("A2").End(xlDown))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mick" wrote in message
...
Hi

I need to save a range in a variable for later use in an
autofill function.
It fails on the autofill line as the value in rng is returned
as TRUE and not
the range data I expected.

I have tried different permutations but all that changes is the
error message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

Storing a range in a variable
 

Thanks for the suggestions, however, I am still getting an error on the
Autofill line.

The error is Autofill method of Range class failed. Excel help isn't very
helpful.
Also if I stop the code before this line and print the variable RNG in the
immediate window I get a
Type mismatch error.

Have I missed something somewhere??

Any thoughts would be appreciated.

Regards

Mick

"Chip Pearson" wrote in message
...
CHange
RNG = Range("A2", Range("A2").End(xlDown)).Select
to
Set RNG = Range("A2", Range("A2").End(xlDown))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mick" wrote in message
...
Hi

I need to save a range in a variable for later use in an autofill
function.
It fails on the autofill line as the value in rng is returned as TRUE and
not
the range data I expected.

I have tried different permutations but all that changes is the error
message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

Storing a range in a variable
 

When you inserted that column, your RNG variable changed to point at B2:B###.

You could use that fact and come back one column to the left:

Option Explicit
Sub testme()

Dim RNG As Range
Set RNG = Range("A2", Range("A2").End(xlDown))

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG.Offset(0, -1), _
Type:=xlFillDefault
End Sub

Or you could even just populate that range directly:

Option Explicit

Sub testme()
Dim LastRow As Long
LastRow = Range("A2").End(xlDown).Row
Columns("A:A").Insert
Range("a2:A" & LastRow).Formula = "=STRIPCN(RC[3])"
End Sub

I got rid of the .select.



Mick wrote:

Hi

I need to save a range in a variable for later use in an autofill function.
It fails on the autofill line as the value in rng is returned as TRUE and
not
the range data I expected.

I have tried different permutations but all that changes is the error
message.

The code is as follows:

RNG = Range("A2", Range("A2").End(xlDown)).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=STRIPCN(RC[3])"
Selection.AutoFill Destination:=RNG, Type:=xlFillDefault

Thanks for any suggestions.

--

Dave Peterson

Storing a range in a variable
 

Thank you for all your suggestions. i can now postpone my computers
journey through the window.

Thanks

mick



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Storing a range in a variable
 

Option Explicit
Sub testme()
Dim RNG As Range
Set RNG = Range("A2", Range("A2").End(xlDown))
Columns("A:A")Insert Shift:=xlToRight
rng.Offset(0,-1).FormulaR1C1 = _
"=STRIPCN(RC[3])"
End Sub

Could be Another way.

--
Regards,
Tom Ogilvy


"Mick Smith" wrote in message
...
Thank you for all your suggestions. i can now postpone my computers
journey through the window.

Thanks

mick



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!