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]

84570 cells(t, 3) is a text or numeric value?

 

[Ark68]

Hi Dante Amor, thanks for stepping up!

I always struggle with that possibility?. If I go to the data worksheet, and use =isnumber(and select one of the values in column 3), it returns TRUE. I assume it means the contents are numeric and not text.

Anyone?

 

[DanteAmor]

I would have to review it with your data.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Ark68]

Thank you,
Try this https://drive.google.com/file/d/1hfZ_bVhUY61AH94fdekCLDP7klO3EnWz/view?usp=sharing

I had to remove much of the data for privacy reasons.

 

[DanteAmor]

And the macro?

The file does not have the macro, I need to check what each of the variables contains during the execution.

 

[Ark68]

Oh, that may not be very easy to provide unfortunately. Its part of a very large vba project depending on several workbooks and filled with network paths and userforms. You would be frustrated with all the errors as you dodge all that stuff just to get to here.

But thank you for trying Dante.

It shouldn't be overly complicated ... I personally think its a comparison of unlike data types but I have no way of proving.

         With ws_data   
             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
                    .Range("A" & t) = format(.Range("B" & t), "00000") & format(t - 1, "000")
                    .Range("C1") = "CONTRACT_"
                    .Range("D" & t) = Application.WorksheetFunction.VLookup(rn_1, ws_rd.Range("A:BJ"), 2, False)    'ammendment
                    .Range("E" & t) = Application.WorksheetFunction.VLookup(rn_1, ws_rd.Range("A:BJ"), 3, False)    'activity type
                    .Range("F" & t) = Application.WorksheetFunction.VLookup(rn_1, ws_rd.Range("A:BJ"), 4, False)    'event
                    .Range("G" & t) = Application.WorksheetFunction.VLookup(rn_1, ws_rd.Range("A:BJ"), 11, False)    'customer name
                    .Range("J" & t) = Application.WorksheetFunction.VLookup(temp_fac, ws_fac1.Range("A:I"), 3, False) 'station
                    .Range("K" & t) = Application.WorksheetFunction.VLookup(temp_fac, ws_fac1.Range("A:I"), 4, False) 'type 1 (field, diamond etc)
                    .Range("L" & t) = Application.WorksheetFunction.VLookup(temp_fac, ws_fac1.Range("A:I"), 5, False) 'unit
                    .Range("V" & t) = 0
                    .Range("Y" & t) = .Range("U" & t)
                    .Range("BJ" & t) = Application.WorksheetFunction.VLookup(rn_1, ws_rd.Range("A:BJ"), 12, False)   'customer number
                Next t
         End With

ws_data holds the data, column C has the value I'm counting
rn_1 = 84330 ... checked at the source (ws_data) to be a number using "isnumber"
ws_rd, the file I posted, is the reference data. The values in column A are numbers as verified by "isnumber"
the value of rn_1 is found at row 155 of ws_rd.

 

[DanteAmor]

But I do not get to the search.
This is on another sheet?

[COLOR=#333333]With ws_data[/COLOR]

 

[DanteAmor]

check
rn_1 = 84569
rn_1 = 84570
rn_1 = 84571

and works

But I do not know where you get this from:

rn_1 = .Cells(t, "C")

 

[Ark68]

Yes, ws_data is another sheet. rn_1 is gathered from there and cross referenced with data in the worksheet I sent you.

I don't see where I used that Dante, I'm sorry. I have something similar ...

rn_1 = .Cells(t,3)

This line is extracting the value to count from a loop in worksheet ws_data. The loop checks (runs a count of) all the values in row t column c against the data in column A of ws_rd. I need to do a countif for each rows in ws_data, hence the loop.