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

  • Thread starter Thread starter jmsohn
  • Start date Start date
J

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
 
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
 
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
Click to expand...
 
Back
Top