Converting multiple items to a list

[xzmike]

I've queried an Access table to Excel that I'm trying to turn into a list.

The table:
Case Name Fee1Type $Amt Fee2Type $Amt Fee3Type $Amt
1 Park Sanitation $10 Postal $15 Postal $$4
2 Jones Postal $5 Livery $20 Sanitation $30
(Many more rows and 6 fee types)

The List:
Case Name Fee1Type $Amt Fee2Type $Amt
1 Park Sanitation $10
1 Park Postal $15
1 Park Postal $4
2 Jones Postal $5
2 Jones Livery $20
2 Jones Sanitation $30


My pseudocode will run something like this: (Assume I've got a count on the number of names and I'm starting at line 2, the first name))


Do until current line, column A= ""
Down a line and insert
Back up a line & copy this line, col (Case,Name,Fee1Type,$Amt)
Down a line & paste.

Down a line and insert
Back up 2 lines & copy this line, col (Case,Name,Fee2Type,$Amt )
Down 2 lines & paste.

Down a line and insert
Back up 3 lines & copy this line, col (Case,Name,Fee3Type,$Amt )
Down 3 lines & paste.

Down a line
Loop

Can this be done more efficiently? I tried to figure out how to get the worksheet into an array, but couldn't.

Here's as far as I got. (Option Base 1)

Dim SubdvsnArray(1 to 2, 1 to 3)
dim rows as integer
dim cols as integer

For rows=1 to 2
For cols=1 to 3
SubdvsnArray(rows,cols)=cells((rows+1),col????)
next cols
next rows

The problem is I've got data in columns 4,5,6,7 and beyond & I can't figure out how to load the array since the cols would be out of subscript.

Any help appreciated
Mike

 

[Damon Ostrander]

Hi Mike,

I responded to this on one of your earlier posts:

http://www.mrexcel.com/board2/viewtopic.php?t=75600&highlight=

Damon