help with If statement that counts

[khameleon1]

I've gotten functions just like this working before, but this one is stumping me... I'm probably doing something simple and the fix is right under my nose, but... Alas!

I have a column of data that I want to count non-blank cells that are not colored one of two shades of gray. In the attached file, all the cells I want to count are red, but that is not always the case. No matter what I try, it just counts every cell, returning a value of 93 (number of rows in the data).

Code is as follows right now:


Dim sq2d As Long
Dim r As Range, c As Range
Set r = Range("A2:A" & lastRow)
For Each c In r
If c.Interior.Color <> RGB(192, 192, 192) Or c.Interior.Color <> RGB(128, 128, 128) And Not IsEmpty(c.Value) Then sq2d = sq2d + 1
Next c

Any assistance would be greatly appreciated!
18464

[NoSparks]

shouldn't that be AND instead of OR between the two colors ?

[khameleon1]

Thanks for replying! It's possible, I guess when I reasoned it out in my head, it can't be both colors at the same time, so with that reasoning, I picked "or". :banghead: I very well might be wrong.
I tried switching my code to "And", but this resulted in the count being "0" and I'm actually expecting the result to be "20".

[SamT]

if c.Interior.Color <> RGB(192, 192, 192) _
and c.Interior.Color <> RGB(128, 128, 128) _
and Not IsEmpty(c) _
then sq2d = sq2d + 1

I prefer using ColorIndexes when available.

To see which colors are which ColorIndex,

sub GetInteriors(()
dim i as long

for i = 1 to 56
with range("A:A")
with .Cells(i)
.Value = i
.Interior.ColorIndex = i
end with
end with
next
end sub

Another way is to duplicate the Format Cells Color Picker chart onto a sheet, then

sub GetIndexes()
dim Cel As Range

for each Cel in Range(??:??)
Cel.Value = Cel.Interior.ColorIndex
next
end sub

[khameleon1]

Thank you! This feels closer, but it's still not working...

Now my code looks like this:


For Each c In r
Not IsEmpty(c)
If c.Interior.Color <> RGB(192, 192, 192) And c.Interior.Color <> RGB(128, 128, 128) Then sq2d = sq2d + 1
End If

Visual Basic does not like the Not IsEmpty(c) line. Gives me error "Compile Error: Expected: Line Number or Label or Statement or End of Statement" Any ideas why this would be??

[SamT]

if not IsEmpty(c) then
BTW, I edited that post many times while playing with getting the blue words to be blue. Please, reread it.

[NoSparks]

Thanks for replying! It's possible, I guess when I reasoned it out in my head, it can't be both colors at the same time, so with that reasoning, I picked "or". :banghead: I very well might be wrong.
I tried switching my code to "And", but this resulted in the count being "0" and I'm actually expecting the result to be "20".
Works for me.

[khameleon1]

Hi again,

I tried adjusting my code in the following way:


If Not IsEmpty(c) Then
and

Set r = Range("A2:A" & lastRow)
For Each c In r
If c.Interior.Color <> RGB(192, 192, 192) And c.Interior.Color <> RGB(128, 128, 128) And Not IsEmpty(c) Then sq2d = sq2d + 1
Next c
wc.Cells(2, 9) = sq2d

Oddly enough, both were still returning "0". I re-verified that the cells do not contain spaces, or anything of that nature that may be screwing up the code (i.e. cells NOT actually empty, just appear empty), but that doesn't appear to be the issue. I see that "No Sparks" figured out what I could not, but unfortunately I'm unable to open the file to determine what was done.

[NoSparks]

I added a calculation of lastRow, a message box and made sure it operated on the right sheet. Here's the macro.


Sub Testing()
Dim sq2d As Long
Dim r As Range, c As Range
Dim lastRow As Long

With ActiveSheet
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("A2:A" & lastRow)
For Each c In r
If c.Interior.Color <> RGB(192, 192, 192) And c.Interior.Color <> RGB(128, 128, 128) And Not IsEmpty(c.Value) Then sq2d = sq2d + 1
Next c
End With

MsgBox sq2d

End Sub

[SamT]

NoSparks' Code:
Sub Testing()
Dim sq2d As Long
Dim r As Range, c As Range
Dim lastRow As Long

With ActiveSheet
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("A2:A" & lastRow)
For Each c In r
If c.Interior.Color <> RGB(192, 192, 192) And c.Interior.Color <> RGB(128, 128, 128) And Not IsEmpty(c.Value) Then sq2d = sq2d + 1
Next c
End With

MsgBox sq2d

End Sub



You might also try

... > RGB(128, 128, 128) And c <> "" Then

[khameleon1]

I swear I'm not crazy, but this still is not working. :crying:

Thank you both for continuing to work with me on this... I literally copied and pasted the code from NoSparks above into it's own Sub, and it returned a value of zero. I also tried various iterations with and without ".Value" attached to "c" and SamT's suggestion above to no avail. It always returns either 93 (total number of cells in r) or zero. I have *some* experience with "if" statements at this point in my self-taught programming, but I've never run across anything like this. It's Excel 2010, if that makes any difference??

[NoSparks]

Why don't you post a workbook, an xlsm file, containing the macro, not just a fragment of the macro, and I'm pretty sure we can help sort things out for you.

[khameleon1]

Okay... my Macro has a lot going on... I've been working on it on and off during my spare time for about a month. Hence the clip and small example file.

Basically, a system I work with outputs Pepsi.xlsx and Pepsi Macro.xlsm contains my code. My example above is from the section labeled "SQG Completion" (in the second 1/2 of the macro). The formula that I'm having trouble with is used 8 times in "SQG Completion" code to create a table in the 'Colors' tab, thereby creating a graph from that data.

Now, if you're still here after I stumbled through that explanation, I sincerely appreciate it.

NOTE: I get an error when the Macro runs, but this is from a later step (and another thread in the SumProduct Forum), and doesn't affect this chart's ability to build or this part of my code.1847118472

[SamT]

HEh. Run that GetColors sub post #10. Just set the column address to one next to your existing cells.

I remember seeing that Excel only uses the Colors in the Color Picker. If you RGB a cell, it actually uses the closest color in the Color Picker.

Compare to the two Colors you are using and replace the Color = RGB() with Interior.ColorIndex = #n

[NoSparks]

You're not specifying the sheet for the range r

[Paul_Hossler]

I'm late to the party, but this gives 20 as an answer




Option Explicit

'IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False.
'False is always returned if expression contains more than one variable.
'IsEmpty only returns meaningful information for variants.

Sub test()
Dim lastrow As Long
Dim sq2d As Long
Dim r As Range, c As Range

lastrow = 94

Set r = Range("A2:A" & lastrow)
For Each c In r.Cells
With c
If Len(.Value) > 0 Then
If .Interior.ColorIndex <> 15 And .Interior.ColorIndex <> 16 Then
sq2d = sq2d + 1
End If
End If
End With
Next c
MsgBox sq2d
End Sub

Sub FindColorIndex()
MsgBox Range("A2").Interior.ColorIndex
MsgBox Range("A93").Interior.ColorIndex
MsgBox IsEmpty(Range("A93"))
End Sub

[khameleon1]

Hi everyone,

I switched to using color indexes instead of RGB, but my large Macro is still returning zeros. I tried Paul's macro above and it generates the expected response, so I'm not sure why when I run it in my macro it does not.

The only change from Paul's Test() Sub above and what is in my file is one END IF that I needed to remove to run the macro. It gave me the error "End if without Block If". Would this cause my results to change?

[Paul_Hossler]

Hi everyone,

I switched to using color indexes instead of RGB, but my large Macro is still returning zeros. I tried Paul's macro above and it generates the expected response, so I'm not sure why when I run it in my macro it does not.

The only change from Paul's Test() Sub above and what is in my file is one END IF that I needed to remove to run the macro. It gave me the error "End if without Block If". Would this cause my results to change?

We'd have to see your macro, but I'm assuming you integrated my little macro into a larger one??

But in general, if you changed something that previously gave the correct results, and not it doesn't, I wouldn't be surprised that the change caused the results to change

[khameleon1]

Hi Paul,

Yes, like I said above, my code is quite long, so I tried to condense it down to the relevant parts for this particular issue. Attached is my code and the file it is used on. The xlsm file contains the code, and the xlsx is the file that has the data in it. (The xlsx file is an output from another system, so I cannot combine into one file easily.)

Note: there is a system error (which I'm trying to address in a different thread) for a later step when the code executes. This shouldn't interfere with the example on this thread though.

1849318494

EDIT: Forgot to mention, the example from this thread is about 2/3-3/4 of the way through the code in a section labeled "SQG Completion"

[NoSparks]

khameleon1,

Comment out the ScreenUpdating = False at the beginning of your code
Put a break point on the first Set r = Range(whatever) ~~~~~ you have about 8 of them and are not specifying the sheet.

You'll discover that the active sheet is not the sheet you want.
You're using a range in the middle of "Graphical Summary"

[khameleon1]

Thank you! That did it! I think when I simplified it down for the example, multiple sheets were not used, and when I tried to re-integrate, I forgot to include this fact again. Thank you so much for the help!!