VBA: Countif

[willsmith919]

I am trying to get a list to populate and count store visits based on dates. I've got a lengthy macro that will populate all of my lists, but my Countif will only work for the first value that populates and will return a 0 for all other values. The code I'm using shortened is:

Dim storeCount As Integer
Dim xRow As Integer
Dim newDate as Date

xRow = 1
Do Until Sheet3.Cells(xRow, 1) = Empty
xRow = xRow + 1
Loop

storeCount = Sheet3.Application.WorksheetFunction.countIf(Range("C1:C" & xRow), newDate)

As I am running through all the other steps in my macro, the first time I run through storeCount will come in as the correct #, but as it loops through to the next newDate variable (code for this is not included, I can post it if needed) storeCount comes back as 0. I've checked that my range is staying consistent. Any ideas why this is happening?

 

[JoeMo]

Not sure what the problem is w/o seeing all your code, but you might try making this change:

storeCount = Application.WorksheetFunction.countIf(Sheet3.Range("C1:C" & xRow), newDate)

 

[Snakehips]

willsmith919,

Welcome to MrExcel.

Your loop is doing nothing but increment xRow?

You need to take storeCount = ..... inside the loop and do something with it from inside the loop?

xRow = 1
Do Until Sheet3.Cells(xRow, 1) = Empty
storeCount = Sheet3??!.Application.WorksheetFunction.countIf(Sheet3??.("C1:C" & xRow), newDate)
'use current value of storeCount here

xRow = xRow + 1
Loop

Hope that helps.

 

[JoeMo]

willsmith919,

Welcome to MrExcel.

Your loop is doing nothing but increment xRow?

You need to take storeCount = ..... inside the loop and do something with it from inside the loop?

xRow = 1
Do Until Sheet3.Cells(xRow, 1) = Empty
storeCount = Sheet3??!.Application.WorksheetFunction.countIf(Sheet3??.("C1:C" & xRow), newDate)
'use current value of storeCount here

xRow = xRow + 1
Loop

Hope that helps.

I think the loop is an attempt to find the last row and then apply the countif to the range from C1 to the last filled row in column C. Not the most efficient way to be sure ...

 

[willsmith919]

Thank you for your help. I will post all of my code and see if you can figure out what is going on, the reason I am using sheet3.application is that I need to do the count if based on sheet3, maybe I need to activate the sheet before.

Sub findDate()
Dim newDate As Date
Dim pRow As Integer
Dim cRow As Integer
Dim lRow As Integer
Dim hRow As Integer
Dim oldCount As Integer
Dim storeCount As Integer
Dim storeNumber As Integer
Dim storeName As String
Dim xRow As Integer

Sheet3.Activate
xRow = 1
Do Until Sheet3.Cells(xRow, 1) = Empty
xRow = xRow + 1
Loop

xRow = xRow - 1
pRow = 7
cRow = 3
newDate = Sheet3.Cells(cRow, 3)

Do Until Sheet3.Cells(cRow, 3) = 0


storeCount = Sheet3.Application.WorksheetFunction.countIf(Range("C1:C" & xRow), newDate)

Do Until Sheet1.Cells(pRow, 2).Value = newDate Or Sheet1.Cells(pRow, 2).Value = Empty
pRow = pRow + 1
Loop

If Sheet1.Cells(pRow, 2) = newDate Then
oldCount = Sheet1.Cells(pRow, 12).Value
oldCount = oldCount + storeCount
Sheet1.Cells(pRow, 12) = oldCount

Else
If newDate < Date Then Sheet1.Cells(pRow, 1) = "Past Date"

If newDate > Date Then Sheet1.Cells(pRow, 1) = newDate - Date

Sheet1.Cells(pRow, 2) = newDate
storeName = Sheet3.Cells(cRow, 5)
Sheet1.Cells(pRow, 4) = storeName
Sheet1.Cells(pRow, 6) = "Sodastream/MarketStar"
Sheet1.Cells(pRow, 8) = "SodaStream Machine"
Sheet1.Cells(pRow, 9) = 632972499
Sheet1.Cells(pRow, 10) = "SodaMix"
Sheet1.Cells(pRow, 12) = storeCount
Sheet1.Cells(pRow, 13) = "Vendor performed demo 25.00 fee"
Sheet1.Cells(pRow, 14) = "Mike Matauic"
Sheet1.Cells(pRow, 15) = "12/12/2013"
Sheet1.Cells(pRow, 16) = "Yes"
Sheet1.Cells(pRow, 17) = "MikeM@sodastream.com"
Sheet1.Cells(pRow, 18) = "n/a"
End If
Sheet2.Activate
Sheet2.Columns("A:E").Select
Sheet2.Range("E1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1:I3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Columns("B:B").ColumnWidth = 39.71
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit

Sheet2.Cells(2, 2) = storeCount
lRow = cRow
hRow = 4
Do Until Sheet3.Cells(lRow, 3) <> newDate
Sheet2.Cells(hRow, 1) = 632972499
Sheet2.Cells(hRow, 2) = "SodaStream"
Sheet2.Cells(hRow, 3) = newDate
storeNumber = Sheet3.Cells(lRow, 6).Value
Sheet2.Cells(hRow, 4) = storeNumber
lRow = lRow + 1
hRow = hRow + 1
Loop

Do Until Sheet3.Cells(cRow, 3).Value <> newDate
cRow = cRow + 1
Loop

newDate = Sheet3.Cells(cRow, 3).Value

Loop

formattingMacro

Sheet1.Activate


Basically my client wants to be able to put a standard list into sheet 3 and have it populated in the forms the have specified in sheets 1 and 2, by using a macro.

 

[Snakehips]

I think the loop is an attempt to find the last row and then apply the countif to the range from C1 to the last filled row in column C. Not the most efficient way to be sure ...

Quite right Joe. I'll put that down to being past bed time if that's ok.

Will, maybe it is the late hour because I'm struggling to picture what's happening.

You get newDate initially from sheet3 C7 and then subsequently from C8 etc yet you are wanting to doing the countif on sheet3 column C ????

As Joe and I pointed out earlier, surely you need to edit the storeCount = line?

storeCount = Sheet3.Application.WorksheetFunction.countIf(Range("C1:C" & xRow), newDate) will return 0

What happens if you use

storeCount = Application.WorksheetFunction.countIf(Sheet3.("C1:C" & xRow), newDate)

Sorry that's not going to be a great deal of help. Off to bed!!!