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

 

[Henneshoe]

I think you need to provide more information. From what I can understand:

Step 1: Click a button
Step 2: Add some rows to the spreadsheet. Will this number be fixed or should the user enter a number?
Step 3: Do you want to prompt the user for data? In your example "number of pipes." How should this data be used by the macro.
Step 4: Click another button. Where is this button?? What should be accomplished by pressing it?
Step 5: Create a matrix of X rows and Y columns in Excel. Are the number of rows and columns fixed?
Step 6: The user enters a bunch of data into the matrix.

Are you having problems with specific commands, understanding the VBA editor, or how the flow of your program should work?

 

[rugbyboy123]

first of all thankz for replying,

i just want from the user a value ( say 3 systems)

presses a button and a table appears on the spreadsheet

- step one from the programme take this number and use it for amount of rows needed.column 1 should have all the pipe systems in e.g

pipe system 1

pipe system 2

pipe system 2

the second column has "amount of pipes needed" as a title

which underneath has three empty spaces for the user to input how many pipes in each system

and right at the end it should have a "total amount of pipes".

another button appears syaing "done" once all the spaces have been filled press that.

and a table for each system appears on the spreadsheet
e.g
cl c2 c3 c4 c5
pipe system 1
1.0
1.1
1.2
(these will depend on how many pipes you said in the first table)

the rest of the columns will all be blank for user to input more data i.e size ,gradient

so for this example you will have 3 tables appear after you click "Done"

Therefore in all the tables the columns are fixed it just the row changes for what ever the user inputs

- all buttons are on the spreadsheet,when you press it the programme is run

- yes the macro/programme should be using the input data

problems understanding commands / VBA

im not a programmer

thankyou for your time, its very helpfull of you

regards

rugbyboy

 

[Henneshoe]

Ok, I understand where you are going with this. Basically you are going to need 2 macros, aka subroutines. The first will be called when the user enters a number of systems and hits the go button. This subroutine will read the value in a designated cell (B2 in this case) and dump out the table starting in row 4.

Sub make_system_table()
    Dim total_rows As Integer
    Dim row_num As Integer
    Dim counter As Integer
    
    total_rows = Cells(2, 2).Value
    Cells(4, 2).Value = "amount of pipes needed"
    
    row_num = 4
    For counter = 1 To total_rows
        Cells(row_num, 1).Value = "Pipe System " & counter
        row_num = row_num + 1
    Next counter
        
End Sub

The second subroutine will be called after the user enters the number of pipes into the table and hits the second “go” button. This subroutine will be a bit larger. It will need to step through the first table and create a bunch other tables. Generally, when I write a larger program I start from the bottom and go up. For yours we can start with the final output, a set of tables.

The function for creating each table, I’ll call it make_pipe_table, needs some inputs. They are:
1) Title – In your example: Pipe System 1. Every Title will start with “Pipe System” so we only need to input the number.
2) Beginning Row # - Assuming the tables will be aligned vertically you will need to know what row to start in.
3) Number of Rows, which equals the total number of pipes in that system.

For convenience in making the next table we will also return a value back from the function. This is the ending row number. Inside this function we can add all the information to the excel worksheet.

Function make_pipe_table(system_num As Integer, row_num As Integer, total_pipes As Integer) As Integer

    Dim counter As Integer
    
    Cells(row_num, 1).Value = ("Pipe System " & system_num)
    row_num = row_num + 1
    For counter = 1 To total_pipes
        Cells(row_num, 1).Value = (system_num & "." & counter)
        row_num = row_num + 1
    Next counter
    
    make_pipe_table = row_num

End Function

Now you will need the subroutine that will read through the initial table and call the function above.

Sub make_tables()

    Dim total_pipes As Integer
    Dim input_row_num As Integer
    Dim output_row_num As Integer
    Dim system_num As Integer
    
    system_num = 1
    input_row_num = 5
    output_row_num = 21
    While Cells(input_row_num, 1) <> ""
        total_pipes = Cells(input_row_num, 2).Value
        output_row_num = make_pipe_table(system_num, output_row_num, total_pipes)
        output_row_num = output_row_num + 1
        system_num = system_num + 1
        input_row_num = input_row_num + 1
    Wend
    
End Sub

Hopefully that will give you a good framework to start with. You will still need to add any formatting to make things pretty. Feel free to ask if you have any further questions.

 

[yakpol]

Try without macros. The following formula will give a number of used cells below (and including) A1.

Number_used_rows = COUNT(OFFSET("$A$1",0,0,Max_number_of_rows,1))

 

[rugbyboy123]

Henneshoe,

i want to thankyou so much, that was wicked !!!!
just a few more questions

1) for the second table ( when i click "go") how do i transferr it onto a diiferent sheet because for big system it will look a bit messy??

2)the pipe number starts from 1.1,1.2,1.3
how do i make it start from 1.0,1.1,1.2 instead i think its something to do with the integers??

3)how can i make it look more like a table i.e lines around it, maybe bit shading

A really do want to thankyou for your time you have been great

kindest regards

rugbyboy123

 

[Henneshoe]

Yeah, I probably should have put some comments in there for some of that stuff. Oh well.

1) To add the pipe tables to a second worksheet you need to edit the make_pipe_tables function. Anywhere you see "Cells(row_num, 1).Value" replace with "Sheets("sheet2").Cells(row_num, 1).Value" where sheet2 is the name of the worksheet you want to put the tables on. Additionally, you may want to change the inital value of output_row_num in the make_tables subroutine to 1 as this is the row which it will start creating the tables.

2) The easiest way to change the index is to replace the following text in the make_pipe_tables function:
(system_num & "." & counter) goes to (system_num & "." & counter - 1)

3) All of the cell formating objects are accessed using the Cells object. For example, if you want to add a border around a cell you would say

Sheet("sheetName").Cells(row, column).borders(edge).LineStyle =  xlContinuous

sheetName = (optional) only needed if the sheet where you want to change the format is not the active sheet
row = row number
column = column number A=1,B=2,C=3...
edge = a constant defined by Microsoft. It basically discribes which edge of the cell you want to turn the border on. Lookup xlBordersIndex in MS help for the names (examples: xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft)
xlContinuous = Another MS constant. xlContinuous gives you a basic border.

Shading is done with Cells(row,column).Interior.ColorIndex = colornumber where color number is the number of the predefined colors in excel.

You change the borders and shading once cell at a time, so it is easiest to do it within the for loop where you write the values into the cell.

There are so many options for formatting that it is hard to post them all here. Most of this stuff can be looked up in Google once you know the terms, so hopefully this will give you a good start. Generally when looking up VBA command I start my google inquery with the words VBA and Excel. Hope that helps.

 

[pmover]

rugbyboy123,

take a moment and review this useful website:

http://www.ozgrid.com/Excel/arrays.htm

Good Luck!
-pmover

 

[rugbyboy123]

how do i create a drop down menu containg numbers inside the code, so that it just appears when i click the macro ??

 

[rugbyboy123]

iv used the macro record button and got With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$N$6:$N$19"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

now i just want to put that into cell(row_num,2)

how do i do this???

please help ???

 

[Henneshoe]

Short Answer: Replace "With Selection.Validation" with "With Cells(row_num,2).Validation"

Longer Answer:
The "With" keyword in the first line of code means that each method below it should be done on the object Selection.Validation. Method is just a fancy name for command. Examples of methods in your macro are: Delete, Add, and IgnoreBlank.

The Selection object returns whatever is highlighted in the spreadsheet. So if you click on cell B3 and run the macro created by the recorder it will add the validation rules to cell B3. The selection object can be very handy in macros and if you end up writing more it will probably be useful at some point. By replacing "Selection" with "Cells(row_num,2)" we are telling the macro to apply the validation rules to that specific cell.

Hope that clears things up.

 

[rugbyboy123]

yep it works. thankyou.
few more questions to stimulate the old brain

1) the pipe table goes to "sheet2" but the user still has to click on the "sheet2 tab" i think the code is

sheet("sheet2").select to automatically take user to that sheet
but dont no where to put it in regards to the code

2)when im testing it and i want to run throught it all again i have to manually delte everything. is there a simple bit of code which just deltes everything and starts again???-- could i just use macro recoreder and setup another button

kind regards
its really annoying me

 

[Henneshoe]

1) You are correct. That is the correct code. I would put it at the end of the make_tables() subroutine. Right after Wend.

2) I have never used the macro recorder, so I am not sure what its capabilities are, but it seems like something it could do. If you want to write something yourself, you could place something like this in your code.

for row_num = 1 to max_rows
   for column_num = 1 to max_column
      Cells(row_num, column_num).Value = ""
   next column_num
next row_num

where max_rows = the lowest row you wish to erase and max_column = the column furthest right that you wish to erase.
There may be more elegant or efficient ways to do it; I am by no means a VBA guru. However, I think this code is pretty straight forward and runs pretty fast as long as your not erasing large (1000 rows x 1000 columns) amounts of data.

 

[rugbyboy123]

yep everything works and i understand where you are coming from . thankyou.

if i wnated to add to cells and call it sum_num

sum_num = cells ( 6,5).value +cells(6,7)
then i want to

cal_1 = sum_cell * 100 /1.2

how would i add that in to main function would i need to add to my orignal function my_table(sum_num as integer, cal_1 as integer)
what about

dim sum_num as integer
dim cals_1 as integer

like dim counter ???

or do i put it into the sub function, i wnat my answer to go into

Cells(3, 6).Value = (cal_1)

hope that makes sense i think im nearly there just need to identify my parameters correctly i think

many regards

 

[rugbyboy123]

sorry iv just looked over it again,

i want that in a totally new macro/button so for every 1.0, 1.1 1.2

i wanted it to add two cells then do some maths to it

then place into a new cell

regards

rugbyboy123

 

[Henneshoe]

Sorry about the delay in reply. On Saturday I returned to the USA after a 3 month business trip in Europe. I have been busy getting things in order.

Have you already figured out a solution for this one or do you still have some questions?

 

[rugbyboy123]

thats no problem at all,

yer iv sorted that out, took about 9 hours to do ... but i wont forget it no real questions at the moment

cheers henneshoe for all your help so far....., you have been reall helpfull

kindest regards

rugbyboy

 

[rugbyboy123]

got question,

if i wanted to create a graph for each pipe 0.1 and so on.
say the equation was y=x+a

and say "a" was a number from a cell i.e (row_num -1,2) which related to that pipe. the x values were 1,2,3,4 so on

this is all in a new macro "final table"


a graph

1.0 56 (just here)-needs to be quite small
1.1 7 """
1.2 11 """

2.0 11 """
2.1 3 """

and so on

how would i do this in vba.

many regards

 

[rugbyboy123]

one more question,

just want to add total pipes i.e 1.0,1.1,2.0,2.1,2.2

in a cell will give 5 pipes


sum_1 = .Sum(Range("A8:A30"))


Sheets("sheet1").Cells(3, 2).Value = PIPE_TOTAL

this doesnt seem to work

any thoughts??

 

[Henneshoe]

As far as your first question goes, I have never used VBA to make graphs in Excel, so I can't help. For your second question I am somewhat confused. You have multiple tables on the sheet (1 table for each system), and each table has header rows you are going to want to ignore along with blank rows between the tables. It is going to be hard to create a formula which parses through all of this information to only find cells that contain "X.Y" where X and Y are your pipe system number and instance number. Why not do a sum of the numbers the user enters in the original table?