Tom,
Can never thank you enough for all of the help that you provide.
Wondered if you could help me a little further on this one....
Is it possible to have the records span across on a row basis (e.g.
transpose the data across the columns, instead of down the rows?).
Also, there will be several headers within the sheet (this is the part which
the "+BV" string indicates, and therefore when the next 'header' appears
Sheet2's data will be returned on a new row).
e.g.
Column A Column B Column C
Row 2: Header 1 (+BV), Value 1 (+BY), Value 2 (+BY)...........
Row 3: Header 2 (+BV), Value 1 (+BY), Value 2 (+BY)...........
Also, how easy would it be to include an additional criteria to include
other possible values that may exist between each +BV entry? (e.g. currently
the only value is defined by +BV, it may be that there could be a +BA entry -
however, I'm still working through my datasource @ the moment so this is
something I'm still looking @).
Thanks again Tom, many thanks - Al.
"Tom Ogilvy" wrote:
Sub ABC()
dim rng as Range, i as Long
Dim k as Long, cell as Range
Dim rng1 as Range
set rng = worksheets("sheet2").Range("A2")
i = 1
set rng1 = Range(cells(1,1),cells(rows.count,1).End(xlup))
for each cell in rng1
if instr(1,cell,"+BV",vbTextCompare) 0 then
rng(i).Value = application.substitute(cell,"+BV","")
i = i + 1
for k = cell.row + 1 to rng1.Rows(rng1.rows.count).row
if instr(1,cells(k,1).Value,"+BY",vbTextCompare) 0 then
rng(i-1,2).Value = application.Substitute(cells(k,1),"+BY","")
exit for
end if
Next
end if
Next
End Sub
However, depending on how your data is set up, this could put duplicate +BY
entries on the second sheet.
--
Regards,
Tom Ogilvy
"Mackay 1979" wrote in message
...
Could anyone assist with a problem I've got trying to 'tidy' up a data
source
I'm working wth. I'm currently importing a file (dat file) into Excel.
I need to be able to loop through the file and perform the following:
1) Loop through for each entry that begins +BV and contains additional
data
in the cell, not = ENSTRH.
e.g. Cell A5 = "+BV Header Test"
When this appears create a new sheet and put the data from this cell into
Sheet2:A2
(If possible it would be useful to not then include the +BV)
2) Continue looping through the entrys and do the same for any entries
with
+BY, but put at the side of the one above (e.g. "+BY Value x" exists, so
this
would then offset from Cell A5 and get put into B5.
3) The next +BV entry (as per Step 1) will then go onto the next row and
so
on...
Thanks for your help on this. Al ( )