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

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

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

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

Have you checked out Conditional Formating?

This can be down without building code and can apply to one or more cells in
row when
applied to a full range.

--
steveB

Remove "AYN" from email to respond
"Stewart" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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
>
>
>

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