Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
Vacuum Sealed presented the following explanation :
If UCase$(vData(i - 1)) = "NO" Then vaData(i, 2) = _ Application.WorksheetFunction.CountIf(rngNames, vData(i - 1)) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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 "". 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
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 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a condition to VBA CountIF
On 26/10/2011 2:07 AM, Rick Rothstein wrote:
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 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) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif with a condition and contains @ | Excel Worksheet Functions | |||
COUNTIF - Condition | Excel Discussion (Misc queries) | |||
COUNTIF - more than one condition | Excel Worksheet Functions | |||
How do I set up a COUNTIF with more than one condition? | Excel Programming | |||
Adding an OFFSET condition to a COUNTIF?? | Excel Programming |