Multipple copy relative value into columns

[twaccess]

Apologies for the not very descriptive title.

I work with large spreadsheets which receive imports via text files i
the following format.

8TH SPINE 1
File name : C:\
Prices : UK List 08/08/2003
Currency : £
26/02/2004
** PRODUCT GROUP : Alliance - misc 9- items
36 SU-SR313701 678 24408
14 SU-SR313702 1285 17990
7 SU-SR313704 803 5621
4 SU-SR313705 1420 5680
72 SU-SR313706 40 2880
61 MM-150.0804M K9 0 0
8TH SPINE 2
File name : C:\
Prices : UK List 08/08/2003
Currency : £
26/02/2004
** PRODUCT GROUP : Alliance - misc 9- items
18 SU-SR313701 678 12204
13 SU-SR313702 1285 16705
4 SU-SR313703 1631 6524
14 SU-SR313704 803 11242
9 SU-SR313705 1420 12780
77 SU-SR313706 40 3080
54 MM-150.0804M K9 0 0
20 MM-SR321001 0 0
8TH SPINE 3
File name : C:\
Prices : UK List 08/08/2003
Currency : £
26/02/2004
** PRODUCT GROUP : Alliance - misc 9- items
10 SU-SR313701 678 6780
12 SU-SR313702 1285 15420
2 SU-SR313703 1631 3262
8 SU-SR313704 803 6424
9 SU-SR313705 1420 12780
56 SU-SR313706 40 2240
39 MM-150.0804M K9 0 0
10 MM-SR321001 0 0

What I would like to do is, insert a left hand column, then copy th
first cell of each page down to the left of each line of product lik
this. (I do it manually at the moment)

8TH SPINE 1
File name : C:\
Prices : UK List 08/08/2003
Currency : £
26/02/2004
** PRODUCT GROUP : Alliance - misc 9- items
8TH SPINE 1 36 SU-SR313701
8TH SPINE 1 14 SU-SR313702
8TH SPINE 1 7 SU-SR313704
8TH SPINE 1 4 SU-SR313705
8TH SPINE 1 72 SU-SR313706
8TH SPINE 1 61 MM-150.0804M K9
8TH SPINE 2
File name : C:\
Prices : UK List 08/08/2003
Currency : £
26/02/2004
** PRODUCT GROUP : Alliance - misc 9- items
8TH SPINE 2 18 SU-SR313701
8TH SPINE 2 13 SU-SR313702
8TH SPINE 2 4 SU-SR313703
8TH SPINE 2 14 SU-SR313704
8TH SPINE 2 9 SU-SR313705
8TH SPINE 2 77 SU-SR313706
8TH SPINE 2 54 MM-150.0804M K9
8TH SPINE 2 20 MM-SR321001

This then means I can quickly sort and clean up the surplus heade
information to provide me with a database in the normal sense.

The lines of data vary in length, but the format is always the same an
I would like to have been able to write a macro which copies the cel
and then crawls along the qty list to work out where to put the copie
cell. Unfortunately all attempts to do this using Macro recorder hav
failed and as always I suspect there is a quick and easy way to d
this.

Is anyone able to help with a simple routine that can do this please ?

Thanks


Terr

 

[Patrick Molloy]

The folowing code should het you started. Copy it into a
new standard module.
Its a simple loop. Find a line with the word "spine",
finf the product line, then add th espine to the first
column until another spine is found.

Option Explicit

Sub ALterTable()

Dim SpineName As String
Const SPINE As String = "SPINE"
Const PRODUCTGROUP As String = "**PRODUCTGROUP"
Dim ThisRow As Long
Dim KeyColumn As String
Dim ThisCellValue As String

' first uinsert the colunm
Columns(1).Insert

'set the start row
ThisRow = 1
' and column
KeyColumn = "C"
' remember we inserted a column!

' now loop through the whole column
ThisCellValue = Cells(ThisRow, KeyColumn).Value
Do Until ThisCellValue = ""
If InStr(ThisCellValue, SPINE) > 0 Then
' new spine
SpineName = ThisCellValue
'now loop to the product group
Do
ThisRow = ThisRow + 1
ThisCellValue = Cells(ThisRow,
KeyColumn).Value
Loop Until InStr(ThisCellValue, PRODUCTGROUP)

0

ThisRow = ThisRow + 1
End If

' add the spine name to column 1
Cells(ThisRow, 1).Value = SpineName
'remove excess data in columns 4-6
Range(Cells(ThisRow, 4), Cells(ThisRow, 6)).Clear


' next row
ThisRow = ThisRow + 1
ThisCellValue = Cells(ThisRow, KeyColumn).Value
Loop

End Sub


Patrick Molloy
Microsoft Excel MVP

 

[twaccess]

Thanks Patrick for your help. This looks useful, please may I ask what
is meant by a=20 ?

Thanks

Terry

 

[Patrick Molloy]

I didn't post "a=20"....typically a newsgroup viewer might show this instead
of a LF or CR instruction. Try reading the mail with say OutLook Express or
Google in explorer