Arrays and range references in VBA
 

I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Arrays and range references in VBA
 

Stewart wrote:
I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Try it with Range("A1:A" & x).Interior.ColorIndex = 39 (untested)

Alan Beban

Arrays and range references in VBA
 

why not put the .Interior.ColorIndex line within the If statement?
Sub gg()
Dim t(40000) As Range, z As Long
For x = 1 To 40000
If Condition Then
z = z + 1
Set t(z) = Range("A1")(x)
t(z).Interior.ColorIndex = 39 '<--- like this
End If
Next

End Sub

or you would have to create another For statement...

Sub gg()
Dim t(40000) As Range, z As Long
For x = 1 To 40000
If Condition Then
z = z + 1
Set t(z) = Range("A1")(x)
End If
Next
'Below line creates another For Statement
For z = 0 To UBound(t, 1)
t(z).Interior.ColorIndex = 39
Next

End Sub


Ronin
"Stewart" wrote:

I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Arrays and range references in VBA
 

Yes, that is correct, but it takes too long (<--relative) to format all of
those individually even with screenupdating turned off.
thanks

"Ronin" wrote:

why not put the .Interior.ColorIndex line within the If statement?
Sub gg()
Dim t(40000) As Range, z As Long
For x = 1 To 40000
If Condition Then
z = z + 1
Set t(z) = Range("A1")(x)
t(z).Interior.ColorIndex = 39 '<--- like this
End If
Next

End Sub

or you would have to create another For statement...

Sub gg()
Dim t(40000) As Range, z As Long
For x = 1 To 40000
If Condition Then
z = z + 1
Set t(z) = Range("A1")(x)
End If
Next
'Below line creates another For Statement
For z = 0 To UBound(t, 1)
t(z).Interior.ColorIndex = 39
Next

End Sub


Ronin
"Stewart" wrote:

I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Arrays and range references in VBA
 

Alan Beban wrote:
Stewart wrote:

I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks


Try it with Range("A1:A" & x).Interior.ColorIndex = 39 (untested)

Alan Beban

Should be Range("A1:A" & x - 1).Interior.ColorIndex = 39

Alan Beban

Arrays and range references in VBA
 

Hi Stuart,

The basic answer is you can't format a range array in one go. Some
alternatives:

1. Format the current true range in the loop, as suggested by Alan and Ronin

2. Union True ranges and format the "big" range.

Problem with union is if the resulting big range results in a large number
of discontiguous areas (say a few hundred) it will be slow. Ie, loop & union
1000 adjacent cells is fast, but 500 alternate cells slow, and exponentially
slower with more. So combine 1 & 2. Continue union'ing adjacent true
ranges. When you get to a false range, format the previous big union range.
Set the big range to nothing and start again.

With 1 & 2, it might be worth first building a Boolean array. Format the
entire range with whatever is the larger qty of true's/false's, then go back
and format the smaller number individually.

3. Conditional formatting, if viable.

4 Autofilter. Insert a new row at the top for header (s). If your True
condition is a common value simply filter on that value, format, and remove
the filter. If not a simple value, devise a formula to put in an adjacent
column to evaluate to true/false. Use Autofill if appropriate to maintain
relative addressing, Filter the formula column and format required adjacent
columns.

With your 40k cell loop & formats I suspect the Filter method would be by
far the fastest (other than CF).

Regards,
Peter T

"Stewart" wrote in message
...
I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Arrays and range references in VBA
 

Peter T wrote:
Hi Stuart,

The basic answer is you can't format a range array in one go. Some
alternatives:

1. Format the current true range in the loop, as suggested by Alan and Ronin
. . . .

I wasn't suggesting that the color formatting be done "in the loop";
simply that t().Interior.Color.Index = 39 in the pseudocode below, which
occurs after the loop has been completed, be replaced by
Range("A1:A" & x-1).Interior.Color.Index = 39

Alan Beban

Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks

Arrays and range references in VBA
 

Thanks you all for the replies. As always, this is a helpful group.

"Stewart" wrote:

I want to do something similar to this. Can it be done?
Sub gg()
Dim t(40000) As Range, z as long
z=0
For x = 1 To 40000
If CONDITION THEN
z=z+1
Set t(z) = Range("A1")(x)
end if
Next
t().Interior.ColorIndex = 39
End Sub


Thanks