Workbook A will check a table in Workbook B if it has the same date from Workbook A, and calculate the total number of rows with that date. At the moment, the table in Workbook B is completely fresh, with only one blank row.

I first test input the Excel formula in a cell:

=COUNTIF('stroke history .xlsm'!Records[Date],H3)

The result gave me 0, no identical date . I then proceed to program in VBA

Function openHistory() As Boolean

'get the date in entry excel.
'pick 1st row.
Dim Dt As Date
Dt = wsCopy.ListObjects("Master").ListColumns("Date").DataBodyRange(1)

'set range of detination refernce
Dim destDate As Range
Set destRng = wsDest.ListObjects("Records").ListColumns("Date").DataBodyRange
'check if entry date already exist in history records
Dim historyChk As Integer

historyChk = WorksheetFunction.CountIf(destRng, Dt)

If historyChk > 0 Then

    MsgBox "This date already exist in records. Possible Duplicate."
    Exit Function

End If

openHistory = True   
Debug.Print historyChk  

End Function

During testing, I get an Invalid Procedure Call or Argument error on the WorksheetFunction row.

During troubleshooting, I experimented by entering a random value in the destination DataBodyRange and then deleting it (BACKSPACE), before running the code again. It worked without having the error.

It’s as if the formula in VBA cannot work when the destination is valueless, which is weird since I could get a zero value in Excel, what may be the cause of this?

Khám phá các thẻ bài đăng