VBA CountIf and Match Formulas not returning correct results (matching values)

[brewitbold]

Hi!

I have been using this forum for so long - yet never had the occasion to post myself.

I am working on a macro that will (among other tasks) determine if a string is present in a range located within the same sheet. I have attempted using the Application.Worksheetfunction.Match and Application.Worksheetfunction.CountIf functions to no avail. Here is the issue I am encountering: the below formula returns "Not New" for every record, even though I know that some of the strings DO NOT currently appear in the range below. This means that the CountIf function is returning > 0 for all records.

When I use the COUNTIF formula within the sheet it confirms this - some records return 1 and other records return 0. I have also tested this with MATCH and sure enough, the correct records will return the relative position of the string with the range, while others return an expected error.

I suspect the issue stems from how VBA reads the string vs. how the formula reads the string, but I have been banging my head against the wall on this error all day.

Thank you in advance.

Sub test_concatenation()


Dim i As Integer
Dim leaveUniqueId As String
Dim rng As Range
Dim idInArray As Integer
Dim recordStatus As Variant


Set rng = ActiveSheet.Range("o5:o50")


idInArray = Application.WorksheetFunction.CountIf(rng, leaveUniqueId)

For i = 2 To 18
    leaveUniqueId = (Cells(i, 1) & Cells(i, 4) & Cells(i, 5) & CLng(Cells(i, 7)))
    Cells(i, 8).Value = leaveUniqueId
        If idInArray = 0 Then
            recordStatus = "New Record"
        Else
            recordStatus = "Not New"
        End If
    Cells(i, 9).Value = recordStatus
        
 
Next


End


End Sub

 

[RoryA]

Welcome to the forum.

Your COUNTIF line needs to be inside the loop and after the line that assigns a Value to leaveUniqueId.

Also, you should probably remove that End line unless you put it there for a good reason.

 

[brewitbold]

Bravo!

Don't know how I missed that, thank you so much!

 

[RoryA]

Glad to help.