variable column numbers for autofill

[mattybinv]

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

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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

>