Countif Statement Showing Incorrect Value

[Ark68]

           With ws_data    
                .Range("J:J").Clear
                For t = 2 To nwb_LastRow
                    rn_1 = .Cells(t, 3)
                    temp_fac = .Range("H" & t) & .Range("I" & t)
                    If Application.WorksheetFunction.CountIf(ws_rd.Range("A:A"), rn_1) = 0 Then
                        MsgBox "Rental does not exist in the database." & Chr(13) & "You were given lots of notice about processing missed rental information." & Chr(13) & "Now you're paying the price. Process terminated.", vbCritical, "CRITICAL ERROR : Missing Rental Data"
                        Exit Sub
                    End If
           End With

rn_1 = 84570
This value is found in column A of worksheet ws_rd at row 211.

Is anyone able to suggest why the countif statement (in blue) is failing? It proceeds through the code as though the value doesn't exist (countif = 0)

ws_rd is recognizing the correct worksheet.

Thank you in advance!

 

[DanteAmor]

That is what you also have to review, the data of the other sheet.


rn_1 = .Cells (t, 3)


That on the other sheet is a number?

Which number of the other sheet is not found in column A

 

[Ark68]

So, the data at the source, ws_data, from where rn_1 is acquired, are numbers. 84430 is at ws_data.cells(1,3) and according to (isnumber) comes back TRUE to being a number.

Book1
	A	B	C	D	E
1	REC_ID	DATE		AMMNT	TYPE
2		21-05-2019	84330	TRUE
3		21-05-2019	84333
4		21-05-2019	83962
5		21-05-2019	84593
6		21-05-2019	84595
7		21-05-2019	83962
8		21-05-2019	83962
9		21-05-2019	83965
10		21-05-2019	83963
11		21-05-2019	83963
12		21-05-2019	84304
13		21-05-2019	84299
14		21-05-2019	85140
15		21-05-2019	85141
DATA

Cell Formulas
Range	Formula
D2	=ISNUMBER(C2)

84430 is found in the reference worksheet ws_rd at row 155 and apparently is also a number

Book1
	A	B	C
151	84317	1	FR
152	84318	0	GM
153	84319	0	FR
154	84320	5	FR
155	84330	TRUE	DR
156	84331	2	DR
157	84332	0	DR
158	84333	7	DR
159	84334	0	DR
160	84335	2	DR
161	84338	10	DR
162	84339	3	DR
Rental_Data

Cell Formulas
Range	Formula
B155	=ISNUMBER(A155)

Unless I am using isnumber wrong, I am struggling to figure out why I can't find a match?

 

[Ark68]

I tried this to knowing that there was a match between the two sheets ...

gh = Application.WorksheetFunction.Match(rn_1, ws_rd.Range("A:A"), 0)

But received an "Unable tyo get the Match property of the WorksheetFunction class" error. I suppose that means it couldn't find a match.

 

[DanteAmor]

I put 84330 on another sheet, I executed the macro piece and it does count.

try:

             Set b = ws_rd.Range("A:A").Find(rn_1, LookIn:=xlValues, lookat:=xlWhole)
             If Not b Is Nothing Then
                MsgBox "Exists!"
             Else
                MsgBox "Dont"
             End If

 

[Marcelo Branco]

So, the data at the source, ws_data, from where rn_1 is acquired, are numbers. 84430 is at ws_data.cells(1,3) and according to (isnumber) comes back TRUE to being a number.

I am coming late to this discussion, but just to confirm: looking at your data in post12, shouldn't it be?
m_1 = ws_data.cells (2,3)
Or is this a typo?

M.

 

[DanteAmor]

I am coming late to this discussion, but just to confirm: looking at your data in post12, shouldn't it be?
m_1 = ws_data.cells (2,3)
Or is this a typo?

M.

Yes, it's a typo error. I tried with my data and it does count, but in the OP sheets it does not count. I do not have the real information to review it.

 

[DanteAmor]

rn_1 How do you have declared this variable?

 

[RoryA]

What does

msgbox Application.WorksheetFunction.CountIf(ws_rd.Range("A:A"), 84570)

show if added to your code before the If test?

 

[DanteAmor]

rn_1 How do you have declared this variable?

If You Dim'med the 'i' variable as Integer... the maximum number an Integer variable can hold is 32767
You should Dim that variable as Long (maximum possible value of 2147483647)