SpecialCells and a Special Character
 

I use
Set rng = .Columns(5).SpecialCells(xlConstants, xlTextValues)
to test for text values.

I have a special character defined by the variable CatoChar
which is dimmed as a String.

Can the above code be adapted to find CatoChar, please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004

SpecialCells and a Special Character
 

Stuart

You can loop through the cells and find only those cells with CatoChar.
Here's an example

Sub CatoOnly()

Dim Rng As Range
Dim CatoRng As Range
Dim FndRng As Range
Dim FirstAdd As String
Const CatoChar As String = "great"

Set Rng = Sheet1.Columns(5).SpecialCells(xlCellTypeConstants , xlTextValues)

Set FndRng = Rng.Find(CatoChar)

If Not FndRng Is Nothing Then
FirstAdd = FndRng.Address
Do
If CatoRng Is Nothing Then
Set CatoRng = FndRng
Else
Set CatoRng = Union(CatoRng, FndRng)
End If

Set FndRng = Rng.Find(CatoChar, FndRng)
Loop Until FndRng.Address = FirstAdd
End If

MsgBox CatoRng.Address

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Stuart" wrote in message
...
I use
Set rng = .Columns(5).SpecialCells(xlConstants, xlTextValues)
to test for text values.

I have a special character defined by the variable CatoChar
which is dimmed as a String.

Can the above code be adapted to find CatoChar, please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004

SpecialCells and a Special Character
 

Many thanks.

That seems quite fast. Will have to find some way to use
it in my routine.

Regards.

"Dick Kusleika" wrote in message
...
Stuart

You can loop through the cells and find only those cells with CatoChar.
Here's an example

Sub CatoOnly()

Dim Rng As Range
Dim CatoRng As Range
Dim FndRng As Range
Dim FirstAdd As String
Const CatoChar As String = "great"

Set Rng = Sheet1.Columns(5).SpecialCells(xlCellTypeConstants ,
xlTextValues)

Set FndRng = Rng.Find(CatoChar)

If Not FndRng Is Nothing Then
FirstAdd = FndRng.Address
Do
If CatoRng Is Nothing Then
Set CatoRng = FndRng
Else
Set CatoRng = Union(CatoRng, FndRng)
End If

Set FndRng = Rng.Find(CatoChar, FndRng)
Loop Until FndRng.Address = FirstAdd
End If

MsgBox CatoRng.Address

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Stuart" wrote in message
...
I use
Set rng = .Columns(5).SpecialCells(xlConstants, xlTextValues)
to test for text values.

I have a special character defined by the variable CatoChar
which is dimmed as a String.

Can the above code be adapted to find CatoChar, please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004