It's very strange! Please review and give me a clue

[jmsohn]

I coded as this below.
When I run DemoUsedRange, no problem!
But when I add CellNumRandum, the error occured.(Application-defined or
object-defined error)
Please give me some comments.

Thanks.


' My Code

Option Explicit

Sub DemoUsedRange()
Dim cel As Range
Dim str As String

For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then str = str & cel.Address & ","
Next

ActiveSheet.Range(Left(str, Len(str) - 1)).Select ' Error
Selection.Font.ColorIndex = 3
End Sub

Sub CellNumRandom()
' Range("A1:J24").Select
Dim cel As Range
For Each cel In ActiveSheet.UsedRange

Randomize

Dim random As Long
random = Int((100 - (-100) * Rnd) - 100)
If random < 30 Then random = -random

cel.Value = random
Next cel
DemoUsedRange ' call upper procedure
End Sub

 

[Tom Ogilvy]

Your string is probably too long.

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
Dim rng as range
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then
if rng is nothing then
set rng = cel
else
set rng = union(rng,cel)
end if
End if
Next
if not rng is nothing then
rng.ColorIndex = 3
' if you want to select
rng.Select
end if
End Sub

or

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then cell.Interior.ColorIndex = 3
Next
End Sub

 

[jmsohn]

Many thanks, Ogilvy!




Tom Ogilvy ÀÛ¼º:

Your string is probably too long.

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
Dim rng as range
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then
if rng is nothing then
set rng = cel
else
set rng = union(rng,cel)
end if
End if
Next
if not rng is nothing then
rng.ColorIndex = 3
' if you want to select
rng.Select
end if
End Sub

or

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then cell.Interior.ColorIndex = 3
Next
End Sub