Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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" wrote in message ... 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" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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" wrote in message om... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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 ------------------------------------------------------------------------ aka_krakur's Profile: http://www.excelforum.com/member.php...o&userid=36905 View this thread: http://www.excelforum.com/showthread...hreadid=337311 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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 ------------------------------------------------------------------------ aka_krakur's Profile: http://www.excelforum.com/member.php...o&userid=36905 View this thread: http://www.excelforum.com/showthread...hreadid=337311 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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 ------------------------------------------------------------------------ aka_krakur's Profile: http://www.excelforum.com/member.php...o&userid=36905 View this thread: http://www.excelforum.com/showthread...hreadid=337311 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help, simple question about variables
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 ------------------------------------------------------------------------ aka_krakur's Profile: http://www.excelforum.com/member.php...o&userid=36905 View this thread: http://www.excelforum.com/showthread...hreadid=337311 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
VBA question about Variables | Excel Programming | |||
Variables - simple question | Excel Programming | |||
simple question, hopefully a simple answer! | Excel Programming |