VBA CountIf issue

Question

I have a problem with countif function in vba(last two rows of code). The it has to calculate certain values in certain range, however it counts absolutely every value as mp3 and nothing as mp4.

The weirdest is that when I copy the procedure into a new separate module, without passing variables, everything works correctly, returning me the right values. What is wrong here? Thanks

Private Sub FindValue(ByVal fileno As String, ByVal deadline As String)

Dim i As Integer
Dim xWs As Worksheet
Dim rng As Range
Dim olApp As Object
Dim olMail As Object
Dim var As Integer
Dim user As String
Dim uplDate As String
Const olMailItem = 0
Dim lastrow As Long
Dim mp3 As Long
Dim mp4 As Long

Sheets("Add_User_ID").Select
var = Sheets("Add_User_ID").Range(("A2"), Sheets("Add_User_ID").Range("A2").End(xlDown)).Rows.Count
If fileno = "" Then
fileno = InputBox("File number")
End If
If deadline = "" Then
deadline = InputBox("Deadline, any format(e.g. 27th of August EOD)")
End If
uplDate = Format(Date, "YYYYMMDD")

For i = 2 To var + 1
user = Sheets("Add_User_ID").Cells(i, "A").Value
Sheets("allocation").Select
With ActiveSheet
    .AutoFilterMode = False
    .Range("A:I").AutoFilter
    .Range("A:A").AutoFilter field:=1, Criteria1:=user
End With

Sheets("allocation").Range("A1").Select
Set rng = ActiveCell.CurrentRegion
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
rng.Copy Destination:=xWs.Range("A1")
lastrow = xWs.Cells(Rows.Count, 2).End(xlUp).Row - 1
mp3 = WorksheetFunction.CountIf(Range(Cells(2, 4), Cells((lastrow + 1), 4)), 3)
mp4 = WorksheetFunction.CountIf(Range(Cells(2, 4), Cells((lastrow + 1), 4)), 4)

Vityata · Accepted Answer · 2017-08-23T12:27:52.240

3

Change the end of the code to this:

with xWs
    mp3 = WorksheetFunction.CountIf(.Range(.Cells(2, 4), .Cells((lastrow + 1), 4)), 3)
    mp4 = WorksheetFunction.CountIf(.Range(.Cells(2, 4), .Cells((lastrow + 1), 4)), 4)
end with

Wtihout the With and the ., the Cells and Range refer to the activesheet. Thus, it is a good idea to refer it yourself.

Then if you feel like it, refactor your code like this:

Change "" to vbNullString Is there any difference between vbNullString and ""?
Change Integer to Long everywhere Why Use Integer Instead of Long?
Make sure that you always refer to the worksheet you are working with and avoid ActiveSheet and Select. How to avoid using Select in Excel VBA
Think of a solution for autoindent - RubberDuck or SmartIndenter

edited Aug 23 '17 at 12:27

answered Aug 23 '17 at 12:16

Vityata

42,633
8
55
100

thanks, the macro itself works properly, the problem appears on the step with mp3 and mp4 calculation. it calculates the proper range, where let's say 250 cells with value 3, and 500 with number 4, but countif for nr 3 returns me result of 750 and countif of nr 4 returns value 0, what is absolutely wrong. even when I've updated the code a/t your recommendations, it still doesn't count properly:( – muahahahh Aug 23 '17 at 12:24
@muahahahh - did you change it, adding the `.` and the `With`? – Vityata Aug 23 '17 at 12:27

VBA CountIf issue

1 Answers1