Maybe better:

ElseIf LCase(trim(.Cells(iRow, "A").Text)) Like "$*" Then

to eliminate that extra space.

===
I'm betting that that currency/accounting style does that to align stuff in the
cell.

(I don't have any money, so it's not something I usually deal with <vbg.)

Nick Hodge wrote:

Dave

Update...

It appears the accounting format I use has some padding around the $. I'll
take another look.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS

"Nick Hodge" wrote in message
...
Dave

For some reason this doesn't work for me. I tried debugging and the code
that picks up the $* doesn't seem to pick it up, even though you are using
the text property which in the immediate window clearly shows the leading
$.

Any thoughts?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dave Peterson" wrote in message
...
This worked for me in xl2003:

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 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 = 0
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, "A").Value) Like "item*" Then
oRow = oRow + 1
oCol = 1
ElseIf LCase(.Cells(iRow, "A").Text) Like "$*" Then
oCol = 3
Else
oCol = 2
End If

NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

This is an extreme newbie question i'm sure, but here goes. I have
approximately 3500 cells of data, but the data is in one column. For
example it goes:

A1 Item 614371
A2 1/2" Binder, Black
A3 $4.59

like that for about 1,000 items. I need to get the rows that begin with
the word "Item" in column A, the item names in column B, and the prices
(begin with $) in column C. The problem is, some items have no item
number, and thus are only in two rows - item name and price.
How would I go about getting all this data in three columns without
manually entering it all? I'm running Excel 2007 Beta 2.

Thank you very much for your help.

-Josh

--

Dave Peterson



--

Dave Peterson