PDA

View Full Version : Solved: SpecialCells bug



mdmackillop
10-22-2008, 10:06 AM
I'm trying to process all cells in a range which contain constants. If the range is greater than one cell, the code works. If only one cell is found, the code fails. Any thoughts?


With ws
Set rg = .Range(.Cells(7, 2), .Cells(Rows.Count, 2).End(xlUp))
rg.SpecialCells(xlCellTypeConstants).Select
End With

Bob Phillips
10-22-2008, 10:28 AM
What is suposed to fail Malcolm, both buttons on your sheet worked for me.

mdmackillop
10-22-2008, 10:36 AM
Hi Bob,
All constant cells are selected, not 1 as I expected.

georgiboy
10-22-2008, 10:38 AM
Seems to work for me, im running excel 2007 for reference.

mdmackillop
10-22-2008, 10:47 AM
I'm using 2000. The issue is easily solved, I just don't understand the cause.

Bob Phillips
10-22-2008, 11:05 AM
I see, I was expecting a crash.

Looks like anothe Specialcells bug to me.

Are you using Intersect to get around it?

mdmackillop
10-22-2008, 11:49 AM
Hi Bob,
I'm just amending the loop code based on the cell count

For Each tmcel In rg.SpecialCells(xlCellTypeConstants)
If rg.Cells.Count = 1 Then Set tmcel = rg
'etc.
'etc.
If rg.Cells.Count = 1 Then Exit For
Next tmcel

Bob Phillips
10-22-2008, 01:38 PM
I thought of something that you could juts use without testing



Sub Test1()
Dim ws As Worksheet
Dim rg As Range
Dim rg2 As Range

Set ws = ActiveSheet

With ws
Set rg = .Range(.Cells(7, 2), .Cells(Rows.Count, 2).End(xlUp))
MsgBox "Range " & rg.Address & " contains " & rg.Cells.Count & " cell(s)"
Set rg2 = Intersect(rg, rg.SpecialCells(xlCellTypeConstants))
rg2.Select
MsgBox "Range " & rg.Address & " contains " & rg2.Cells.Count & " constant cells"
Range("K1").Select
End With
End Sub

mdmackillop
10-22-2008, 02:03 PM
Thanks Bob,
I'll try it at work tomorrow.