VBA CountIF "Overflow" After Deleting Record

[Rex2024]

Using a vba form, I delete a record and then go to "count" the number of events. Once the record is deleted, the countif is returning an "Overflow" error. I know it's because the range no longer equals the number of records, so how do I correct it?

Sub GetPriorEvents(sEmp)

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("EventLog")
    Dim myRange As Range
    Set myRange = ws.Columns(2)
    NumPriorEvents = Application.WorksheetFunction.CountIf(myRange, "=" & sEmp)

End Sub

 

[Joe4]

Where exactly are you seeing this error?
Do you have a COUNTIF function on your worksheet somewhere?

Or in the code you posted?
If it is in the code you posted, what are you sending for "sEmp"?

We may need to know about how this VBA code is being called, and how you are deleting the data (are you deleting rows or columns)?

 

[Rex2024]

Hey Joe4,

I was responding and I realized the issue based on your question. I was passing a bad variable sEmp to the Sub. It's been fixed.

Thanks for your help!

 

[Joe4]

You are welcome.
Glad I was able to help.