How do i create a table / dynamic array in Excel VBE 1

[rugbyboy123]

Dear all,

i am trying to create a table in excel spreadsheet which the userputs an input data, say 6( or any number ) and from that when they click on a macro button creates the given amount of rows i.e 6 and then the user can add another set of values i.e

how many pipe systems ( user inputs 6)

GIVES after pressing a marco button

how many pipes
pipe system 1 ( enter say 4)

pipe system 2 6

pipe system 3 3

and so on so on

AND THEN FROM THAT

pipe system 1 has 4 pipes which gives

aload of columns which again they have to fill out
1.0
1.1
1.2
1.3

im reallly struggling on the vba code - since i have never used it

regards

rugbyboy

 

[rugbyboy123]

are yes of course i will do that. thankyou.

thanks for all your help i think iam done

 

[rugbyboy123]

i have this code which works......


With Sheets("introduction").Select
Range("I22").Select
ActiveCell.Value = "=SUM(R[-3]C[-3]:R[100]C[-3])"

End With

BUT i want to put it on a different sheet however it give no value , i think its because its an equation specified for that sheet?? is this the best way to sum a colunm in vba and place the answer in a new cell ???

 

[Henneshoe]

I am confused by the format of your With statement. The preferred method to do the exact same thing is:

Sheets("introduction").Select
Range("I22").Select
ActiveCell.Value = "=SUM(R[-3]C[-3]:R[100]C[-3])"

I would say this still isn't optimal because each select statement moves your cursor from around the workbook. I have seen large macros written this way and they take forever to execute because you are constantly refreshing the data on the screen. If you prefer the Range method you could do the following:

Sheets("introduction").Range("I22").Value = "=SUM(R[-3]C[-3]:R[100]C[-3])"

If you want a summation from a different page, you can add that to the sum function:

    Sheets("introduction").Range("I22").Value = "=SUM(sheet2!F21:F27)"

Where introduction is the sheet where the sum will be placed and sheet2 is the sheet where the range you are summing is located. For this example I used F21 to F27 as the cells to sum.

 

[rugbyboy123]

again thankyou i understand now.