CODEWITHSUNDEEP
arraysexcelvbarange
user3790788
user3790788

Reputation: 53

Referencing Ranges in Arrays

I have a code that turns cells white if they are gray, and stores that range in an array. Then I check each range in that array to see whether it is empty and the pattern is solid (white). If it is, then I turn it a lighter shade of gray. My code to test for this is currently

Dim Prev() As Range
'I also tried Prev() As Variant and have the same problem
Dim PrevCount As Long
Dim Counter As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Interior.Pattern = xlGray50 Then
    Target.Interior.Pattern = xlSolid
    PrevCount = PrevCount + 1
    ReDim Preserve Prev(1 To PrevCount)
    Set Prev(PrevCount) = Target
End If

For Counter = LBound(Prev) To UBound(Prev)
    If IsEmpty(Prev(Counter)) And Prev(Counter).Interior.Pattern = xlSolid  Then
        Prev(Counter).Interior.Pattern = xlGray25
    End If
Next

End Sub

No error occurs, but none of the ranges in the array that fulfill both criteria turn gray. I know nothing is wrong with the array, because it works just fine if I just do

For Counter = LBound(Prev) To UBound(Prev)
    If Prev(Counter).Interior.Pattern = xlSolid  Then
        Prev(Counter).Interior.Pattern = xlGray25
    End If
Next

So the 

If IsEmpty(Prev(Counter))

Part does not work and I'm not sure why.

Upvotes: 1

Views: 57

Answers (2)

hnk
hnk

Reputation: 2214

I suspect the IsEmpty is returning False always, because it will return True only if variable is not initialized.

You need to do something else like a comparison to zero or empty string depending on your data. Best is you check this

Prev(Counter) = 0

assuming you are dealing with numerical quantities. That should work.

Reviewing the new code there are two points here. The Redim Preserve preserves all global data so it needs to be flushed empty (Redim to 0 elements) once your work is done.

Secondly, you should try setting

Dim Test as Integer
Test = CInt(Prev(x).Value)
if Test = 0 Then .... so on and so forth

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Do not use IsEmpty() rather use:

If Prev(Counter) = "" And Prev(Counter).Interior.Pattern = xlSolid  Then

or

If cStr(Prev(Counter).Value) = "" And Prev(Counter).Interior.Pattern = xlSolid  Then

Upvotes: 0

Related Questions