Excel 2007 : Can Excell create adjustable table (number of columns and rows) ?

[western_gunner]

Hi! this is my first post in the forum,

I want to ask can excell automatically prepares some cells for me to insert my formula? i'm planning to make some kind of calculation based on cartesian coordinate, so my plan is to makes some adjustable tables (in terms of number of columns and rows) for these coordinate..

for example if I enter

m = 5 (in cell B1)
n = 2 (in cell B2)

there will be a table with 2 rows and 5 columns that will automatically adjustable if i enters different values.

i attached a file to ensure my intentions.. do i need a vba coding for this to happen?

Oh and thanks for replying Sorry if this kind of question has been asked before.

[NickyC]

Hi
try this macro

Sub create_table()
Dim RowCell As String, ColCell As String, DestCell As String, TableEnd, n As Long, nZ As Long
RowCell = "B2" 'cell with number of rows
ColCell = "B1" 'cell with number of columns
DestCell = "D24" 'first cell of new table
TableEnd = Range(DestCell).Offset(Range(RowCell).Value, Range(ColCell).Value).Address

For n = 1 To Range(ColCell).Value
    Range(DestCell).Offset(0, n).Value = n
Next n
For n = 1 To Range(RowCell).Value
    Range(DestCell).Offset(n, 0).Value = n
Next n

For nZ = 1 To 4
Range(Range(DestCell).Offset(1, 1).Address, TableEnd).Borders(nZ).LineStyle = xlContinuous
Next nZ

End Sub

[western_gunner]

Thanks for replying NickyC,

I'm not used to VBA or macro's, so sorry if i ask alot, i want to know about:

"For nZ = 1 To 4
Range(Range(DestCell).Offset(1, 1).Address, TableEnd).Borders(nZ).LineStyle = xlContinuous
Next nZ"

what are those line for?

Thanks before.

[NickyC]

Hi
this is to replicate the appearance of the table in your spreadsheet - you had lines around the cells
in excel, Borders(1) is the left border, Borders(2) is the top, etc. So looping through 1 to 4 is marginally quicker than specifying each border separately.
If you don't want the lines, just delete that bit of code

[western_gunner]

thanks again NickyC and OOT, do you know any good source to learn macro's basic command like you did?

[NickyC]

this forum is a pretty good place to start - lots of idea

there are also online sites that help

perhaps the best thing to do is to practice. Start by recording a few macros and looking at the code that excel produces automatically, it will help you to see how the comands work

good luck