automaticaly create a variable sized table from user input

[tigger]

Hi,
How can I user data input to a cell by a user to create a table with that
number of rows.

eg. User enters 1024 in B2
165 in B3
12.5 in B4


A10 would contain 1, B10 would contain =B3+(A10*$B$4)
A11 would contain 2, B11 would contain =B3+(A11*$B$4)
etc to 1024

The user entered number in B2 could be any whole number between 1 and 1024

Thanks,
Iain

 

[Don Guillett]

I could make this better but other things to do right now so try this.
Uncomment the last line to remove the formula and just leave the values.

Sub makeformula()
Range("a10") = 1
Range("b10").Formula = "=B3+(A10*$B$4)"
Set frng = Range("a11:a" & Range("b2"))
With frng
.Formula = "=A10+1"
.Offset(, 1).Formula = "=$b$3+(a11*$b$4)"
'.Formula = .Value
End With

End Sub

 

[Biff]

Hi Iain!

So the max length of the table is 1024 rows?

In A10 enter this formula: =IF(B2<>"",1,"")
In B10 enter this formula: =IF(A10="","",B$3+(A10*B$4))
In A11 enter this formula: =IF(A10<B$2,A10+1,"")
Drag copy the formula in A11 down to row 1033.
Now select cell B10 and double click the fill handle.

Biff

 

[tigger]

Many thanks to both of you for your quick responses. Biff's method will work
a treat, Don's not quite right but gives me a very good starting point.

Iain