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
-----Original Message-----
Apologies for the not very descriptive title.
I work with large spreadsheets which receive imports via
text files in
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 the
first cell of each page down to the left of each line of
product like
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 header
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 and
I would like to have been able to write a macro which
copies the cell
and then crawls along the qty list to work out where to
put the copied
cell. Unfortunately all attempts to do this using Macro
recorder have
failed and as always I suspect there is a quick and easy
way to do
this.
Is anyone able to help with a simple routine that can do
this please ?
Thanks
Terry
---
Message posted from http://www.ExcelForum.com/
.