How do i create a table / dynamic array in Excel VBE
How do i create a table / dynamic array in Excel VBE
(OP)
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
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





RE: How do i create a table / dynamic array in Excel VBE
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?
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
CODE
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.
CODE
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.
CODE
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.
RE: How do i create a table / dynamic array in Excel VBE
CODE
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
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
CODE
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.
RE: How do i create a table / dynamic array in Excel VBE
take a moment and review this useful website:
http://www.ozgrid.com/Excel/arrays.htm
Good Luck!
-pmover
RE: How do i create a table / dynamic array in Excel VBE
RE: How do i create a table / dynamic array in Excel VBE
.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 ???
RE: How do i create a table / dynamic array in Excel VBE
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.
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
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.
CODE
for column_num = 1 to max_column
Cells(row_num, column_num).Value = ""
next column_num
next row_num
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.
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
Have you already figured out a solution for this one or do you still have some questions?
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
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
RE: How do i create a table / dynamic array in Excel VBE
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??
RE: How do i create a table / dynamic array in Excel VBE
RE: How do i create a table / dynamic array in Excel VBE
thanks for all your help i think iam done
RE: How do i create a table / dynamic array in Excel VBE
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 ???
RE: How do i create a table / dynamic array in Excel VBE
CODE
Range("I22").Select
ActiveCell.Value = "=SUM(R[-3]C[-3]:R[100]C[-3])"
CODE
CODE
RE: How do i create a table / dynamic array in Excel VBE