Countif Statement Showing Incorrect Value

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Rich (BB code):
           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!
 
Last edited:
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
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
ABCDE
1REC_IDDATEAMMNTTYPE
221-05-201984330TRUE
321-05-201984333
421-05-201983962
521-05-201984593
621-05-201984595
721-05-201983962
821-05-201983962
921-05-201983965
1021-05-201983963
1121-05-201983963
1221-05-201984304
1321-05-201984299
1421-05-201985140
1521-05-201985141
DATA
Cell Formulas
RangeFormula
D2=ISNUMBER(C2)


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


Book1
ABC
151843171FR
152843180GM
153843190FR
154843205FR
15584330TRUEDR
156843312DR
157843320DR
158843337DR
159843340DR
160843352DR
1618433810DR
162843393DR
Rental_Data
Cell Formulas
RangeFormula
B155=ISNUMBER(A155)


Unless I am using isnumber wrong, I am struggling to figure out why I can't find a match?
 
Last edited:
Upvote 0
I tried this to knowing that there was a match between the two sheets ...

Code:
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.
 
Upvote 0
I put 84330 on another sheet, I executed the macro piece and it does count.

try:

Code:
             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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
What does

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

show if added to your code before the If test?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top