variable column numbers for autofill
 

Hi,

i have a spreadsheet that is required to autofill a cell in a variabl
number of columns, ie starting at Cell A1 upto say Z1 (And if A1 = 1
therefore Z1 = 26)

Firstly the user is asked to input the number of columns he wants in
text box, this variable should then be used to undertake
"Selection.AutoFill" of "Type:=xlFillSeries".

I can make this work for rows (code below) as the column (ie A, B etc
is constant and i can use the following code for the rows as i a
refering to row numbers: (appologies for the poor code, i am stil
learning!!;) )

Dim answer, answer_2, answer_3 As String
Dim Myrange As Range
Range("B2").Select
ActiveCell.FormulaR1C1 = "0"
Range("B2").Select
answer = array_vert_number.Value
answer_2 = "B" & answer
answer_3 = "B2:" & answer_2
Set Myrange = Range(answer_3)
Selection.AutoFill Destination:=Myrange, Type:=xlFillSeries


The problem comes when i am refering to letters of the colums. The use
might not know that he wants to go up to column EF (whatever number tha
would be!) but he might know that he wants 34 columns, for example.

Can any one help (appologies is these seems confusing!)

Thanks in advance;

--
mattybin
-----------------------------------------------------------------------
mattybinv's Profile: http://www.excelforum.com/member.php...fo&userid=2947
View this thread: http://www.excelforum.com/showthread.php?threadid=49179

variable column numbers for autofill
 

Range("B2").Value = "0"
answer = array_vert_number.Value
ans_cols = 34
Set Myrange = Range("B2:B" & answer)
Range("B2").AutoFill Destination:=Myrange, Type:=xlFillSeries
Myrange.AutoFill Destination:=Myrange.Resize(, ans_cols),
Type:=xlFillSeries


--

HTH

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


"mattybinv" wrote
in message ...

Hi,

i have a spreadsheet that is required to autofill a cell in a variable
number of columns, ie starting at Cell A1 upto say Z1 (And if A1 = 1,
therefore Z1 = 26)

Firstly the user is asked to input the number of columns he wants in a
text box, this variable should then be used to undertake a
"Selection.AutoFill" of "Type:=xlFillSeries".

I can make this work for rows (code below) as the column (ie A, B etc)
is constant and i can use the following code for the rows as i am
refering to row numbers: (appologies for the poor code, i am still
learning!!;) )

Dim answer, answer_2, answer_3 As String
Dim Myrange As Range
Range("B2").Select
ActiveCell.FormulaR1C1 = "0"
Range("B2").Select
answer = array_vert_number.Value
answer_2 = "B" & answer
answer_3 = "B2:" & answer_2
Set Myrange = Range(answer_3)
Selection.AutoFill Destination:=Myrange, Type:=xlFillSeries


The problem comes when i am refering to letters of the colums. The user
might not know that he wants to go up to column EF (whatever number that
would be!) but he might know that he wants 34 columns, for example.

Can any one help (appologies is these seems confusing!)

Thanks in advance;)


--
mattybinv
------------------------------------------------------------------------
mattybinv's Profile:
http://www.excelforum.com/member.php...o&userid=29479
View this thread: http://www.excelforum.com/showthread...hreadid=491790