You're right.
I misread the original post. I thought that the description was the the thing
that could move.
Maybe something like:
Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim oRow As Long
Dim FoundPrice As Boolean
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = 1
FoundPrice = False
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oCol = 1
ElseIf LCase(Trim(.Cells(iRow, "A").Text)) Like "$*" Then
oCol = 3
FoundPrice = True
Else
oCol = 2
End If
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
If FoundPrice = True Then
oRow = oRow + 1
FoundPrice = False
End If
Next iRow
End With
End Sub
Nick Hodge wrote:
Dave
I tried that and it's fine down to the missing Item# and then goes haywire
again. (Put's the $ in position 2).bizarre...
Sure it works for most though as OP hasn't returned with an issue
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS
<<snipped