help, simple question about variables

[Alex]

hello,
i'm just a rookie...

selection.autofill destination.range("A2:A10000")

I'd like to replace the a10000 with a variable...

can help?.
thanks,

Ale.

[Tom Ogilvy]

Dim i as long
Dim sourceRange as Range
Dim fillRange as Range
i = 10000
Set sourceRange = Worksheets("Sheet1").Range("A2")
Set fillRange = Worksheets("Sheet1").Range("A2:A" & i)
sourceRange.AutoFill Destination:=fillRange--
Regards,
Tom Ogilvy

"Alex" <[email protected]> wrote in message
news:[email protected]...
> hello,
> i'm just a rookie...
>
> selection.autofill destination.range("A2:A10000")
>
> I'd like to replace the a10000 with a variable...
>
> can help?.
> thanks,
>
> Ale.

[Tom Ogilvy]

Dim i as long
Dim sourceRange as Range
Dim fillRange as Range
i = 10000
Set sourceRange = Worksheets("Sheet1").Range("A2")
Set fillRange = Worksheets("Sheet1").Range("A2:A" & i)
sourceRange.AutoFill Destination:=fillRange

OR

Dim sStr as String
Dim i as long
Dim sourceRange as Range
Dim fillRange as Range
i = 10000
sStr = "A" & i
Set sourceRange = Worksheets("Sheet1").Range("A2")
Set fillRange = Worksheets("Sheet1").Range("A2:"& sStr)
sourceRange.AutoFill Destination:=fillRange

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:egP%[email protected]...
> Dim i as long
> Dim sourceRange as Range
> Dim fillRange as Range
> i = 10000
> Set sourceRange = Worksheets("Sheet1").Range("A2")
> Set fillRange = Worksheets("Sheet1").Range("A2:A" & i)
> sourceRange.AutoFill Destination:=fillRange--
> Regards,
> Tom Ogilvy
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> > hello,
> > i'm just a rookie...
> >
> > selection.autofill destination.range("A2:A10000")
> >
> > I'd like to replace the a10000 with a variable...
> >
> > can help?.
> > thanks,
> >
> > Ale.
>
>

[Bob Phillips]

Ale,

If the variable holds the string value A2:A1000, then just use

selection.autofill destination.range(myVar)

if the variable just holds the string value of the last cell, then use

selection.autofill destination.range("A2:" & myVar)

but if it just holds that row number, then use

selection.autofill destination.range("A2:A" & myVar)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alex" <[email protected]> wrote in message
news:[email protected]...
> hello,
> i'm just a rookie...
>
> selection.autofill destination.range("A2:A10000")
>
> I'd like to replace the a10000 with a variable...
>
> can help?.
> thanks,
>
> Ale.

[David]

Something like this maybe:
Sub Macro1()
Dim Message, Title, Default, MyValue
Message = "Enter ending address to fill, like 'A10000'"
Title = "Fill address"
Default = "A10000"
MyValue = InputBox(Message, Title, Default)
Range("A2:" & (MyValue)).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False
End Sub

"Bob Phillips" wrote:

> Ale,
>
> If the variable holds the string value A2:A1000, then just use
>
> selection.autofill destination.range(myVar)
>
> if the variable just holds the string value of the last cell, then use
>
> selection.autofill destination.range("A2:" & myVar)
>
> but if it just holds that row number, then use
>
> selection.autofill destination.range("A2:A" & myVar)
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> > hello,
> > i'm just a rookie...
> >
> > selection.autofill destination.range("A2:A10000")
> >
> > I'd like to replace the a10000 with a variable...
> >
> > can help?.
> > thanks,
> >
> > Ale.
>
>
>

[aka_krakur]

I am having a similar need. However, my spreadsheet is being driven by a Query Table; with the exception of columns A thru D are formulas. So when the query is refreshed usually the first few rows still end up containing the formulas.

I am trying to write a macro that autofills by selecting A2:D2 and then fills down to last possible row. This will vary every time the spreadsheet is refreshed according the the criteria for the query. I cannot specify the range as follows:
____________________________
Sub AutoFill()
'
' AutoFill Macro
' Macro recorded 8/9/2006 by dredden
'
' Keyboard Shortcut: Ctrl+q
'
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D20")
Range("A2:D20").Select
End Sub

Is there some way to have the Destination look at Column E and the last row in that column to stop there when that field is blank?

I could really use some help on this....I am a very basic to none user in Macros. I'm trying to use a book, but it's not much help.

[aka_krakur]

I am having a similar need. However, my spreadsheet is being driven by a Query Table; with the exception of columns A thru D are formulas. So when the query is refreshed usually the first few rows still end up containing the formulas.

I am trying to write a macro that autofills by selecting A2:D2 and then fills down to last possible row. This will vary every time the spreadsheet is refreshed according the the criteria for the query. I cannot specify the range as follows:
____________________________
Sub AutoFill()
'
' AutoFill Macro
' Macro recorded 8/9/2006 by dredden
'
' Keyboard Shortcut: Ctrl+q
'
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D20")
Range("A2:D20").Select
End Sub

Is there some way to have the Destination look at Column E and the last row in that column to stop there when that field is blank?

I could really use some help on this....I am a very basic to none user in Macros. I'm trying to use a book, but it's not much help.

[aka_krakur]

I am having a similar need. However, my spreadsheet is being driven by a Query Table; with the exception of columns A thru D are formulas. So when the query is refreshed usually the first few rows still end up containing the formulas.

I am trying to write a macro that autofills by selecting A2:D2 and then fills down to last possible row. This will vary every time the spreadsheet is refreshed according the the criteria for the query. I cannot specify the range as follows:
____________________________
Sub AutoFill()
'
' AutoFill Macro
' Macro recorded 8/9/2006 by dredden
'
' Keyboard Shortcut: Ctrl+q
'
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D20")
Range("A2:D20").Select
End Sub

Is there some way to have the Destination look at Column E and the last row in that column to stop there when that field is blank?

I could really use some help on this....I am a very basic to none user in Macros. I'm trying to use a book, but it's not much help.

[aka_krakur]

I am having a similar need. However, my spreadsheet is being driven by a Query Table; with the exception of columns A thru D are formulas. So when the query is refreshed usually the first few rows still end up containing the formulas.

I am trying to write a macro that autofills by selecting A2:D2 and then fills down to last possible row. This will vary every time the spreadsheet is refreshed according the the criteria for the query. I cannot specify the range as follows:
____________________________
Sub AutoFill()
'
' AutoFill Macro
' Macro recorded 8/9/2006 by dredden
'
' Keyboard Shortcut: Ctrl+q
'
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D20")
Range("A2:D20").Select
End Sub

Is there some way to have the Destination look at Column E and the last row in that column to stop there when that field is blank?

I could really use some help on this....I am a very basic to none user in Macros. I'm trying to use a book, but it's not much help.