Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Arrays
Im trying to drill down a list, coloring cells that do not match any of 13
strings stored in an array. I am sure I am not using the array correctly and in turn am looking for some direction€¦ Appreciatively Arturo Sub FalgUnMatched() Dim X As Integer Dim ListRef As String ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") Range("E4").Select For X = 4 To 8439 If ActiveCell.Value < UBound(ListRef) Then ActiveCell.Interior.ColorIndex = 43 End If ActiveCell.Offset(1, 0).Select Next X End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Arrays
You are storing cell addresses in the arry, not values.
You can match a value like so If Not IsError(Application.Match(Activecell.Value,ListRef ,0)) Then and no need to select Sub FalgUnMatched() Dim X As Long Dim ListRef As String ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") For X = 4 To 8439 If Not IsError(Application.Match(Range("E" & X).Value,ListRef,0)) Then Range("E" & X).ColorIndex = 43 End If Next X End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Arturo" wrote in message ... I'm trying to drill down a list, coloring cells that do not match any of 13 strings stored in an array. I am sure I am not using the array correctly and in turn am looking for some direction. Appreciatively Arturo Sub FalgUnMatched() Dim X As Integer Dim ListRef As String ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") Range("E4").Select For X = 4 To 8439 If ActiveCell.Value < UBound(ListRef) Then ActiveCell.Interior.ColorIndex = 43 End If ActiveCell.Offset(1, 0).Select Next X End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Arrays
Sub FalgUnMatched()
Dim X As Long Dim ListRef As Variant Dim Counter As Integer Counter = 0 ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") For X = 4 To 8439 If IsError(Application.Match(Range("E" & X).Value, ListRef, 0)) Then Range("E" & X).Interior.ColorIndex = 43 Counter = Counter + 1 End If Next X MsgBox Counter End Sub "Bob Phillips" wrote: You are storing cell addresses in the arry, not values. You can match a value like so If Not IsError(Application.Match(Activecell.Value,ListRef ,0)) Then and no need to select Sub FalgUnMatched() Dim X As Long Dim ListRef As String ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") For X = 4 To 8439 If Not IsError(Application.Match(Range("E" & X).Value,ListRef,0)) Then Range("E" & X).ColorIndex = 43 End If Next X End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Arturo" wrote in message ... I'm trying to drill down a list, coloring cells that do not match any of 13 strings stored in an array. I am sure I am not using the array correctly and in turn am looking for some direction. Appreciatively Arturo Sub FalgUnMatched() Dim X As Integer Dim ListRef As String ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3", "$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9") Range("E4").Select For X = 4 To 8439 If ActiveCell.Value < UBound(ListRef) Then ActiveCell.Interior.ColorIndex = 43 End If ActiveCell.Offset(1, 0).Select Next X End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Need help with arrays (I think) | Excel Programming | |||
Trouble with Arrays / Collections | Excel Programming | |||
arrays | Excel Programming |