VBA Excel Multi Dimensional Array

Topic

New Reply

I have a fairly large “master” spreadsheet and I am trying to create a *.csv file (as a separate file) that captures data from one column of the “Master” spreadsheet. Each row in the *.csv file will be a concatenation of some constants and some variable (all separated by commas of course), but underlying the arrangement in the master spreadsheet are two indices I will refer to as “A” and “B”. There are 6 “A” values (1 thru 6) and within each of these there are 53 “B” values (1 thru 53) for a total of 318 combinations. I was hoping to loop using two nested “for next” loops for each of these nested array indices, filling the array with data from the master worksheet as it progressed. Next, I would activate the new *.csv spreadsheet & increment to each row, creating each concatenated row text value from corresponding array and constant data items(row qty = “A” X “B”). My question is how do I set up a two dimensional array or would I be better off just switching the focus back & forth between the two workbooks as I constructed each row’s text (kind of like using the master spreadsheet’s rows and columns as my data container rather than loading an array)?

Reply | Quote

Replies

I have a fairly large “master” spreadsheet and I am trying to create a *.csv file (as a separate file) that captures data from one column of the “Master” spreadsheet. Each row in the *.csv file will be a concatenation of some constants and some variable (all separated by commas of course), but underlying the arrangement in the master spreadsheet are two indices I will refer to as “A” and “B”. There are 6 “A” values (1 thru 6) and within each of these there are 53 “B” values (1 thru 53) for a total of 318 combinations. I was hoping to loop using two nested “for next” loops for each of these nested array indices, filling the array with data from the master worksheet as it progressed. Next, I would activate the new *.csv spreadsheet & increment to each row, creating each concatenated row text value from corresponding array and constant data items(row qty = “A” X “B”). My question is how do I set up a two dimensional array or would I be better off just switching the focus back & forth between the two workbooks as I constructed each row’s text (kind of like using the master spreadsheet’s rows and columns as my data container rather than loading an array)?

What I believe you have described is a worksheet with a 6 cell by 53 cell table. Why not save that sheet as a csv file?

Reply | Quote

What I believe you have described is a worksheet with a 6 cell by 53 cell table. Why not save that sheet as a csv file?

Don,

Thanks for the input. Actually it’s a little more involved than that. The structure of the input to the problem is header info (constants on first couple of rows, various columns) and then two columns of index data followed bay a column of descriptor data selected by the user with a resulting format something like:

A1 B1 Description1
A1 B2 Description2
…
A1 B53 Description 53
A2 B1 Description 54
A2 B2 Description 55
…
A2 B53 Description 106
A3 B1 Description 107
etc…

And the csv output must look like this (it is an import file to a program I have no control over, so no flexibility there):

(Constant 1), (Constant 2), (Constant 3), (A1 value), (B1 Value), (Description1), (Constant 4), (Constant 5)
(Constant 1), (Constant 2), (Constant 3), (A1 value), (B2 Value), (Description2), (Constant 4), (Constant 5)
…
(Constant 1), (Constant 2), (Constant 3), (A1 value), (B53 Value), (Description53), (Constant 4), (Constant 5)
(Constant 1), (Constant 2), (Constant 3), (A2 value), (B1 Value), (Description54), (Constant 4), (Constant 5)
(Constant 1), (Constant 2), (Constant 3), (A2 value), (B2 Value), (Description55), (Constant 4), (Constant 5)
…
(Constant 1), (Constant 2), (Constant 3), (A2 value), (B53 Value), (Description106), (Constant 4), (Constant 5)
(Constant 1), (Constant 2), (Constant 3), (A3 value), (B1 Value), (Description107), (Constant 4), (Constant 5)
etc…

But possibly I could just copy / paste vertical columns of each variable data type to form this structure (I will have to do this in segments as there are interruptions (unwanted rows) in the data every 53 rows. I could then just transfer the constant data into the appropriate columns and fill to the bottom of the active row range. Not the direction I first started in, but I’ll give it a try.

Reply | Quote

But possibly I could just copy / paste vertical columns of each variable data type to form this structure (I will have to do this in segments as there are interruptions (unwanted rows) in the data every 53 rows. I could then just transfer the constant data into the appropriate columns and fill to the bottom of the active row range. Not the direction I first started in, but I’ll give it a try.

I would be inclined to add a new sheet that returns the table in the order you want for the csv file. This need only be done once and will be ready to be saved at any time.

Can you post a copy of the file with sensitive data removed?

Reply | Quote

The attached file provides a working example of what I believe you need. It contains the following code:

Code:

Option Explicit
   
   Public Sub Create_CSV()
   
   'Set the following constants to the required string
   Const C1 = "Constant 1"
   Const C2 = "Constant 2"
   Const C3 = "Constant 3"
   Const C4 = "Constant 4"
   Const C5 = "Constant 5"
   
   'Set the following constants to the appropriate path; filename; and sheetname
   Const Pathspec = "C:Trash"	'Path for the csv file
   Const Filespec = "MyFile.csv"   'csv filename
   Const Sheetspec = "Sheet2"	  'Name of the sourrce sheet
   
   Dim CtrA As Long	' The A index
   Dim CtrB As Long	' The B index
   
	   With Sheets(Sheetspec)
		   Open Pathspec & Filespec For Output As #1
			   For CtrA = 1 To 6
				   For CtrB = 1 To 53
					   Write #1, "(" & C1 & ")", "(" & C2 & ")", "(" & C3 & ")", _
						   "(" & .Range("A" & CtrA) & ")", _
						   "(" & .Range("B" & CtrB) & ")", _
						   "(" & .Range("C" & CtrB) & ")", _
						   "(" & C4 & ")", "(" & C5 & ")"
				   Next CtrB
			   Next CtrA
		   Close #1
	   End With
   End Sub

H.T.H.

Reply | Quote

Mr Wells,

Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the “master spreadsheet” and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the “master” worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?

Reply | Quote

Mr Wells,

Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the “master spreadsheet” and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the “master” worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?

Apologies; the file which I provided lost the form and its code. I will re-post with an updated version this evening. Sorry, but I must go now.

Reply | Quote

Mr Wells,

Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the “master spreadsheet” and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the “master” worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?

The attached has four named ranges; two with the lists of constants, and two with the selection results. I have also added a form with some code.

I recommend stepping through the code to gain an understanding.

Note a similar post was deleted as the file did not contain the form.

Reply | Quote