| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multipple copy relative value into columns
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 -- Message posted from http://www.ExcelForum.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multipple copy relative value into columns
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/ . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multipple copy relative value into columns
Thanks Patrick for your help. This looks useful, please may I ask what
is meant by a=20 ? Thanks Terry --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multipple copy relative value into columns
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 -- Patrick Molloy Microsoft Excel MVP --------------------------------- "twaccess " wrote in message ... Thanks Patrick for your help. This looks useful, please may I ask what is meant by a=20 ? Thanks Terry --- Message posted from http://www.ExcelForum.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a relative cell reference that will copy the format? | Excel Discussion (Misc queries) | |||
| COPY RELATIVE FORMAT | Excel Discussion (Misc queries) | |||
| How do I copy an absolute column and relative row to the right? | Excel Discussion (Misc queries) | |||
| How do I copy a formula with relative ref. keeping the same ref.? | Excel Discussion (Misc queries) | |||
| Using Relative Addresses to Select Columns | Excel Programming | |||
![twaccess[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
