help needed extracting data accross the row into the first cell of the row

[garyusenet]

What i'm trying to achieve is a little tricky to explain so i've tried
to be as thorough as possible! If you want any more information please
ask! I'm depending on your responses, thankyou.

The Worksheet Setup
===========================

Each row contains a unique customer record. As well as customer contact
information, customer order-history is contained within each row.
A (simplified) layout of the worksheet is: -
------------------------------------------------------------------------------------------------------------------------
Name Address Tel Make1 Qty1 Price1 Makel2 Qty2
Price2 --->
Name2 Adress2 Tel2 etc.. --->
.....
------------------------------------------------------------------------------------------------------------------------


Some customers have ordered 90 products and so any given row may
contain hundreds of columns.

What I Need
===========================

I need for each customer (every row) to create an order-history cell,
which combines all of the product order details from the current row.
The history cell is basically a string formatted so that every order
appears on it's own line.

So for each customer I want something like this in the first cell of
their row. (please excuse lack of artistic talent.)

-------------------- A ------------------------
| Order1 Price1 Qty1
|
|
1 | Order2 Price2 Qty2
|
|
| Order3 Price3 Qty3
-------------------------------------------------

What I have so far
===========================

Using the fact that each piece of order information repeats itself
every 43 columns I have been able to make a start. For example the
first 'Make' column (these orders are for cars) appears in the 7th
column. For this reason I know that every 7+43rd column contains model
information.

The following code takes the contents of the seventh, and subsequent
seventh columns of the current row (i.e. all the model data) and puts
them into the first column (A) of the current row. I have added CR's
using Chr(10) so that each column is placed onto a new line as it is
placed in the text string.

this is the code: -
----------------------------------------------------------------------------------------
'setup the variable
dim lastcolumn as long
dim makecolumns as long
dim orderhistory as string

'initialise lastcolumn
lastcolumn = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column

For makecolumns = 7 To lastcolumn Step 42
orderhistory = orderhistory & Chr(10) &(Cells(ActiveCell.Row,
makecolumns).Value)
Next makecolumns
Cells(ActiveCell.Row, "A") = textstring
-----------------------------------------------------------------------------------------
This produces a result something like the following: -

------------------------------
Ford
Vauxhall
Audi
....
------------------------------

What I need
===========================

I need to modify the above so that at each iteration not only is the
make information added to the order history cell, but the quantity and
price information is too. The quantity information starts at column 8
and repeats likewise every 43 columns. The Price information starts at
column 9, again this repeats every 43 columns. What I want to end up
with in the first cell of each row is something like this...

Ford 3 £13,000 (ford taken from 7th
column, 3 taken from the 8th column, 13,000 taken from the 9th
columns.)
Vauxhall 7 £14,000 (vauxhall from the 50th
column, 7 from the 51st column, 13,000 from the 52nd column.)
Audi 9 £19,000 (audi from the 93rd
column, 9 from the 94th column, 19,000 from the 95th column.)
.... ... ... etc...
===

Thanks for taking the time to read this,

please suggest solutions.

Gary.

 

[garyusenet]

Please excuse the type re: step in the code.

thanks again.

Gary.

 

[garyusenet]

Also in the sample table 'price2' is part of the first row - google has
very poor formatting power - sorry.

Thanks,

Gary.