Adding a condition to VBA CountIF

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

Hi All

This code is one of the best codes I use in the stable of codes I have
collected over time which programatically counts or sums multiple columns
without the need for nested formulas, and I have modded it to suit my
purpose, although on this occasion, I need to add a condition for the
CountIf()** section of it.

Each of the ranges have either "OK" or "NO" in their respective columns.
What I need is to only count "NO" whereas in it's current state, it returns
all cells that are not "".

Code:

Sub Process_Drivers()

Dim vData, vaData()
Dim sTemp As String, i As Integer, lRows As Long
Dim rngNames As Range, rngHrs As Range, rngBreaks As Range, rngPreOp As
Range, rngSigned As Range

wksTarget As Worksheet

Set wksTarget = Sheets("Charting")
Set rngNames = Sheets("Summary").Range("$E$5:$E$15000")
Set rngHrs = Sheets("Summary").Range("$G$5:$G$15000")
Set rngBreaks = Sheets("Summary").Range("$H$5:$H$15000")
Set rngPreOp = Sheets("Summary").Range("$I$5:$I$15000")
Set rngSigned = Sheets("Summary").Range("$J$5:$J$15000")

vData = rngNames

For i = 1 To UBound(vData)
If Not InStr(1, sTemp, vData(i, 1), vbTextCompare) > 0 Then _
sTemp = sTemp & "~" & vData(i, 1)

Next

sTemp = Mid$(sTemp, 2): vData = Split(sTemp, "~")

lRows = UBound(vData) + 1: ReDim vaData(1 To lRows, 1 To 5)
vaData(1, 1) = "Drivers Name": vaData(1, 2) = "Hours Worked": vaData(1, 3)
= "Breaks Taken": vaData(1, 4) = "Pre-Op Checks": vaData(1, 5) = "Sheet
Signed"

For i = 5 To lRows

**
vaData(i, 1) = vData(i - 1)
vaData(i, 2) = Application.WorksheetFunction.CountIf(rngNames, vData(i -
1))
vaData(i, 3) = Application.WorksheetFunction.CountIf(rngNames, vData(i -
1))
vaData(i, 4) = Application.WorksheetFunction.CountIf(rngNames, vData(i -
1))
vaData(i, 5) = Application.WorksheetFunction.CountIf(rngNames, vData(i -
1))


Next
wksTarget.Range("$A$3").Resize(UBound(vaData), 5) = vaData

Sheets("Charting").Select

Range("A4").Select
Range("A4:E60").Sort Key1:=Range("A4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("A1").Select

End Sub


Appreciate any pointers..

TIA
Mick
 
Vacuum Sealed presented the following explanation :

If UCase$(vData(i - 1)) = "NO" Then vaData(i, 2) = _
Application.WorksheetFunction.CountIf(rngNames, vData(i - 1))
 
Hi Garry

Apologies for late reply.

Tried your response and came up empty, I realised why too.


If UCase$(vData(i - 1)) = "NO"

This will never equal "NO" as it is the name column.

Also

Can you clarify this statement for me so I better understand how this
section of code works please...

For i = 1 To UBound(vData)
If Not InStr(1, sTemp, vData(i, 1), vbTextCompare) > 0 Then _
sTemp = sTemp & "~" & vData(i, 1)
Next

In the If Not Instr() the criteria is numeric based, "Not Text" yet asks to
compare text when constructing "sTemp", if this were the case then should
not this be ( <>"" ).

As always Garry, I appreciate your time

TIA
Mick
 
Each of the ranges have either "OK" or "NO" in their respective
columns. What I need is to only count "NO" whereas in it's
current state, it returns all cells that are not "".
Click to expand...

I can't help feeling there is a more compact macro available to do what you
want, but I am having trouble visualizing your data layout. Can you describe
where the data you want to count is? Can you also tell us what you want the
output to "look like"? Final question... what is in the cells of the ranges
you want to count... constants or formulas? (If formulas, are those formula
the "same", except for cell references? If so, show us some of them please.)

Rick Rothstein (MVP - Excel)
 
Hi Rick

Layout

E G H I J
Name Hrs Breaks PreOps Signed
Jack OK NO OK OK
Pete NO OK OK NO
John OK OK OK OK

There is up to 28 Drivers covering up to 31 days in the month, and each
months data is copied to an appending ("Summary") sheet - [this section I
have no probs with]...

Essentially, this is a compliance checklist that gets done each day for the
previous days activities.

I am attempting to consolidate a count for each driver and the respective
("G:J") range from the Summary sheet where the driver registers any ("NO")
values.

I have used this particular routine on a couple of other files very
effectively, granted they were all straight forward calculating numerics and
not criteria driven cell counting as is the need on this occasion.

The desired wksTarget result would be:

E G H I J
Name Hrs Breaks PreOps Signed
Jack 0 1 0 0
Pete 1 0 0 1
John 0 0 0 0

When the driver registers a certain overall score a warning letter is raised
and if not corrected, further action is taken until the driver complies with
the Federal & State laws governing the industry activities.

The Source sheet ("Summary") will continue to update with data from each
month, then I run this update code which coallates the (Array, if you like)
again to include the fresh information.

If this is to hard to accomplish, I may just create a ghost section of the
sheet and convert the text values to (0) zeros = "OK" & (1) ones = "NO" and
calculate them that way as the code works fine numerically, just means a
little more work and a larger file size.

Appreciate your time.

Mick.
 
Layout
E G H I J
Name Hrs Breaks PreOps Signed
Jack OK NO OK OK
Pete NO OK OK NO
John OK OK OK OK

The desired wksTarget result would be:

E G H I J
Name Hrs Breaks PreOps Signed
Jack 0 1 0 0
Pete 1 0 0 1
John 0 0 0 0
Click to expand...

I may be missing something, but for what I think you described, why not (in
code) just copy the OK/NO table to the desired wksTarget location and then
use the range's Replace method twice on the entire range, once to replace OK
with 0 and the second time to replace NO with 1?

Rick Rothstein (MVP - Excel)
 
Vacuum Sealed presented the following explanation :
Hi Garry

Apologies for late reply.

Tried your response and came up empty, I realised why too.


If UCase$(vData(i - 1)) = "NO"

This will never equal "NO" as it is the name column.

Also

Can you clarify this statement for me so I better understand how this section
of code works please...

For i = 1 To UBound(vData)
If Not InStr(1, sTemp, vData(i, 1), vbTextCompare) > 0 Then _
sTemp = sTemp & "~" & vData(i, 1)
Next

In the If Not Instr() the criteria is numeric based, "Not Text" yet asks to
compare text when constructing "sTemp", if this were the case then should not
this be ( <>"" ).

As always Garry, I appreciate your time

TIA
Mick
Click to expand...

Since sTemp is a string, InStr() is using vbTextCompare to find the
value in vData(i, 1) [Row(i) of Col(1)]. The point is to only add
unique values to sTemp and restructure vData with only unique values.
Thus, you must check that each value in vData is not already in sTemp,
and if not then add it.
 
I may be missing something, but for what I think you described, why not
(in code) just copy the OK/NO table to the desired wksTarget location
and then use the range's Replace method twice on the entire range, once
to replace OK with 0 and the second time to replace NO with 1?

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks Rick

Looking at it in another way is to use Sumproduct(), the downside is
that would mean I would have to nest each drivers name and apply the
formula to each driver and for each column.

=SUMPRODUCT(--(Summary!"$E5:$E15000"="Jack")--(Summary!$G$5:$G15000"="NO"))

That means lots of work plus if/when any new drivers are added it also
then has to get formulas added also, this code was a great shortcut.

I will keep plugging away at it.

Thx again.
Mick
 
Back
Top