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 ( )