Visual Basic - Multiple Criteria code

Microsoft Excel 2003 (full product)
February 5, 2010 at 12:18:05
Specs: Windows XP
I have 2 columns of information similar to this...
Type............Color
water base....blue with green
oil base........blue without green
oil base........green
..................red
oil base........blue with green
water base....yellow
..................orange
water base....blue without green

... I am trying to count 2 things. 1) I want to count all the colors with blue that are not water base. 2) I want to count all the colors with green that are not water base.

FYI - some of the types are blank for a reason.


See More: Visual Basic - Multiple Criteria code

Report •


#1
February 5, 2010 at 12:52:55
You didn't say "Please", but I'm in a good mood 'cuz it's Friday.

Open the VBA editor, Insert a Standard Module and paste in the code below.

Let's say your list is in A1:B9.

In a cell enter =BlueOil(B1:B9) or = GreenOil(B1:B9)

Option Explicit
Function BlueOil(myPaint As Range)
Dim myCount, cell
 For Each cell In myPaint
  If UCase(cell) Like UCase("*Blue*") And _
     Not UCase(cell.Offset(0, -1)) Like UCase("*WATER*") Then
    myCount = myCount + 1
  End If
 Next
   BlueOil = myCount
End Function

Function GreenOil(myPaint As Range)
Dim myCount, cell
 For Each cell In myPaint
  If UCase(cell) Like UCase("*Green*") And _
     Not UCase(cell.Offset(0, -1)) Like UCase("*WATER*") Then
    myCount = myCount + 1
  End If
 Next
   GreenOil = myCount
End Function



Report •

#2
February 5, 2010 at 12:59:55
You are correct. Forgive my rudeness. Thank you for your quick reply.

I did not consider this approach. I will plug it in and give it a try.

Would you happen to know a shorter method for doing this. I have a lot of these to do for many combinations. I have used the SUM arrays but it just does not work on a few of them.

Thank you again for the assistance. Hope you have a great weekend.


Report •

#3
February 5, 2010 at 13:36:20
You could use a UDF that accepts the list, the word you want and word you don't want as arguments.

Using your example from before:

Put your list in A1:B9
Put Blue in C1
Put Water in D1

=GoodBad($B$1:$B$9, C1, D1)

That should return 2.

You could put green and water in C2, D2 and drag it down.

or use:

=GoodBad($B$1:$B$9, C1, $D$1) and put green in C2, leaving water in D1 to be common to both searches.

Keep in mind that I'm just suggesting things for the example data you provided. Other layouts or other searches may require something very different.

Option Explicit
Function GoodBad(myWords, goodWord, badWord As Range)
Dim myCount, cell
 For Each cell In myWords
  If UCase(cell) Like UCase("*" & goodWord & "*") And _
     Not UCase(cell.Offset(0, -1)) Like UCase("*" & badWord & "*") Then
    myCount = myCount + 1
  End If
 Next
   GoodBad = myCount
End Function


Report •

Related Solutions

#4
February 5, 2010 at 13:57:55
Thank you, much. This solves it. Take care - and keep helping the software needy.

Report •

Ask Question