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?
[VBA]
With ws
Set rg = .Range(.Cells(7, 2), .Cells(Rows.Count, 2).End(xlUp))
rg.SpecialCells(xlCellTypeConstants).Select
End With
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
What is suposed to fail Malcolm, both buttons on your sheet worked for me.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi Bob,
All constant cells are selected, not 1 as I expected.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'm using 2000. The issue is easily solved, I just don't understand the cause.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I see, I was expecting a crash.
Looks like anothe Specialcells bug to me.
Are you using Intersect to get around it?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi Bob,
I'm just amending the loop code based on the cell count
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I thought of something that you could juts use without testing
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks Bob,
I'll try it at work tomorrow.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
