Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing textin a variable and comparing to a second value | Excel Discussion (Misc queries) | |||
Storing a range variable in a cell | Excel Discussion (Misc queries) | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Storing value in a variable from a cell | Excel Programming | |||
Storing a Macro name in a Variable | Excel Programming |