VBA CountIF will not count over entire column

Question

I am in the process of creating a VBA code that will allow users to enter information into a form and then move that data into a database for storage. During the migration of the data, I am attempting to usea worksheetfunction.countif to prevent duplicate "Case ID's" from being entered into the database. When I try to run my code with a known duplicate, it is still allowing the data to be written and does not seem to be counting over the entire column.

I am aware that this question relates to several others already asked but I have reviewed every question I could find and it appears to me that I am doing things according to the already provided answers.

Can someone please tell me why my code will not detect duplicates in column A?

Sub DatabaseBuilder()

Dim CaseID As String
Dim CaseLevel As String
Dim Queries As String
Dim CaseReportData(2 To 8) As String
Dim UniqueIdentifier As String
Dim CaseCount As String


Worksheets("Case Reporting").Select
CaseID = Worksheets("Case Reporting").Cells(2, "D").Value
CaseLevel = Worksheets("Case Reporting").Cells(4, "D").Value
Queries = Worksheets("Case Reporting").Cells(1, "A").Value


For i = 2 To 4
    CaseReportData(i) = Worksheets("Case Reporting").Cells(i, 4).Value
Next i
For i = 5 To 7
    CaseReportData(i) = Worksheets("Case Reporting").Cells(i - 3, 8).Value
Next i
    CaseReportData(i) = Worksheets("Case Reporting").Cells(34, 3).Value



ReDim QueryData(1 To Queries) As String
Dim Count As Integer
Count = 1

For i = 1 To Queries
    UniqueIdentifier = Worksheets("Case Reporting").Cells(Count + 6, 3).Value
    If UniqueIdentifier = i Then
    QueryData(i) = Worksheets("Case Reporting").Cells(Count + 6, 6).Value
    Count = Count + 1
    End If
Next i


Set myData = Workbooks.Open....


Worksheets("Case Data").Select
Worksheets("Case Data").Range("A1").Select
RowCount = Worksheets("Case Data").Range("A1").CurrentRegion.Rows.Count
CaseCount = Application.WorksheetFunction.CountIf(Columns("A"), CaseID)
MsgBox CaseCount

If CaseCount < 1 Then
    For i = 2 To 8
        With Worksheets("Case Data").Range("A1")
        .Offset(RowCount, i - 2) = CaseReportData(i)
    End With

    Next i


    Worksheets("Case Data").Select
    Worksheets("Case Data").Range("H1").Select

    For i = 1 To Queries
        With Worksheets("Case Data").Range("H1")
        .Offset(RowCount, i - 1) = QueryData(i)
    End With
    Next i
End If


ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub

Answer 1

We actually don't need to see all of this code, it was better with the original post, most of it is just noise.

Anyway, the current problem is probably the fact that you are using an implicit reference for Column("A"). Which means that it may not be looking Worksheets("Case Data"), but instead the current sheet in the active workbook.

In fact, the Active Workbook changes when you open the "Database" file, which I'm guessing also has a Worksheet called "Case Data" otherwise the next line would error

You can test this by adding the following line:

Debug.Print Columns("A").Parent.Name 

or even

Debug.Print Worksheets("Case Data").Parent.Name

Originally I said that it should be:

CaseCount = Application.WorksheetFunction.CountIf(Worksheets("Case Data").Columns("A"), CaseID)

but that is wrong, because again I'm using an implicit reference to the Current Workbook

If I were you I'd clean up the code by removing the Select statements and using Explicit references instead of implicit ones (i.e. Sheet.Columns vs Columns).

Dim Book as Workbook
Dim Sheet as Worksheet
Set Book = ThisWorkbook ' Or ActiveWorkbook or Workbooks("File ABC.xlsx")
Set Sheet = Book.Worksheets("Case Data")
With Sheet
    CaseID = .Cells(2, "D").Value
    '''
    CaseCount = Application.WorksheetFunction.CountIf(.Columns("A"), CaseID)
End With