=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")
{=IF(D1="sub-total",SUM(--(E1:H1=0)),"Not A Sub Total Row")}
Sub SFDHourCopyFormating28()
Sheets("Hour").Select
Sheets("Hour").Copy After:=Sheets(12)
Sheets("Hour (2)").Select
Cells.Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.RowHeight = 12
Range("D35").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D36").Select
Selection.Cut
Range("D37").Select
ActiveSheet.Paste
Cells.Select
ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hour (2)").Sort.SortFields.Add2 Key:=Range( _
"D1:D9997"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Hour (2)").Sort
.SetRange Range("A1:IV9997")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R86C8:R113C58,"">90"")"
Range("C2").Select
' Here is where I need the new code to count the zeros
End Sub
Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.Range("C2").Value = "=COUNTIF(addrangehre,0)"
Thank you, but that will count all zeros in a specific range. I can only use the zeros in the sub-total rows. There is 1 sub-total row for each date, and four other rows with many zeros for each date that I can not use. I'm hoping to avoid a separate =COUNTIF formula for each date and have the macro search for the sub-total rows and count the zeros.
Dim wsHour2 As Excel.Worksheet
Set wsHour2 = Sheets("Hour 2")
Dim rngTable1 As Excel.Range
Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table
rngRows = rngTable1.Rows.Count
rngRow1 = rngTable1.Rows(1).Count
Dim i As Long
[COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer
Dim iTotal as Integer
iTotal = 0
For i = rngRow1 To rngRows
If cells (i, 4) = "Sub-Total"
[/COLOR]
iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR])
iTotal = iTotal + iVal
end if
next
Range("C2").Value = iTotal
end sub
Thanks for this!!! I'm getting a syntax error onSounds like you need to run a loop through each line with an if statement.
Code:Dim wsHour2 As Excel.Worksheet Set wsHour2 = Sheets("Hour 2") Dim rngTable1 As Excel.Range Set rngTable1 = wsHour2.Range("A1").CurrentRegion 'change A1 to the first cell in the table rngRows = rngTable1.Rows.Count rngRow1 = rngTable1.Rows(1).Count Dim i As Long [COLOR=#0000FF]Dim[/COLOR] iVal [COLOR=#0000FF]As Integer Dim iTotal as Integer iTotal = 0 For i = rngRow1 To rngRows If cells (i, 4) = "Sub-Total" [/COLOR] iVal = Application.WorksheetFunction.COUNTIF(Range([COLOR=#CC0000]"A1:A10"[/COLOR]),[COLOR=#CC0000]"0"[/COLOR]) iTotal = iTotal + iVal end if next Range("C2").Value = iTotal end sub
If cells (i, 4) = "Sub-Total"
There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".Thanks for this!!! I'm getting a syntax error on
Code:
If cells (i, 4) = "Sub-Total"
If Cells(i, 4) = "Sub-Total" Then
Thank you! Getting closer!There are a few errors with that line of code. There should not be a space after "cells", and it is missing a "Then".
Try:
Code:If Cells(i, 4) = "Sub-Total" Then