Thread
:
Excel 2007 B2 - Selecting every cell that starts with a certain word
View Single Post
#
5
Posted to microsoft.public.excel.misc
Nick Hodge
external usenet poster
Posts: 1,173
Excel 2007 B2 - Selecting every cell that starts with a certain word
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
Reply With Quote
Nick Hodge
View Public Profile
Find all posts by Nick Hodge