Inserting rows
 

To Anyone who can help,

I have a three columns I am working with and a list of addresses i
contained in the third. Each address listing ends in a phone number
which is listed as

(***)***-****

I would like to insert a row after each phone number by searching fo
the first parenthesis "(". The ActivateCell function is not working a
hoped. Here is the vba I have thus far:


Worksheets("sheet1").Activate
Range("C1").Select

Dim newrng As Range, newcell As Range
Set newrng = Columns(1).SpecialCells(xlConstants, xlTextValues)
For Each newcell In newrng
If Left(newcell.Value, 1) = "(" Then
ActiveCell.EntireRow.Insert
End If
Next

Thanks for any help

--
Message posted from http://www.ExcelForum.com

Inserting rows
 

Kilcup,

Change
If Left(newcell.Value, 1) = "(" Then
ActiveCell.EntireRow.Insert

To:
If Left(newcell.Value, 1) = "(" Then
newcell(2).EntireRow.Insert

HTH,
Bernie
MS Excel MVP

"Kilcup " wrote in message
...
To Anyone who can help,

I have a three columns I am working with and a list of addresses is
contained in the third. Each address listing ends in a phone number,
which is listed as

(***)***-****

I would like to insert a row after each phone number by searching for
the first parenthesis "(". The ActivateCell function is not working as
hoped. Here is the vba I have thus far:


Worksheets("sheet1").Activate
Range("C1").Select

Dim newrng As Range, newcell As Range
Set newrng = Columns(1).SpecialCells(xlConstants, xlTextValues)
For Each newcell In newrng
If Left(newcell.Value, 1) = "(" Then
ActiveCell.EntireRow.Insert
End If
Next

Thanks for any help!


---
Message posted from http://www.ExcelForum.com/

Inserting rows
 

Thanks for the help! It worked just fine.
Jef

--
Message posted from http://www.ExcelForum.com