Runtime Error for finding dates

Hi,

I have an Excel Workbook, in this i have multiple worksheets. I have defined a Row Range where all the dates are defined of a month and in other sheet i have defined a table range where bank holidays dates are defined. In both these i have defined the same date format as dd-mmm-yy.

The dates in the Row Range are calculated by formulas, where as the dates entered in the Bank Holiday Table Range are manually.

The issue is when i try to find the date from Bank Holiday Databodyrange in the Row Range it gives me error code 91. Below is my code and excel sheet format.

Bank Holiday Table
-----------
Bank Holidays
-----------
01-Jan-19
26-Jan-19

Row Range
-------------------------------------------------
31-Dec-18 | 01-Jan-19 | 02-Jan-19|
-------------------------------------------------

Sub Cal()

    Dim wb As Workbook
    Dim aws As Worksheet, ws As Worksheet
    Dim eidtbl As ListObject, tbl As ListObject
    Dim eidarr As Range, eidv As Range, eidcel As Range, amtcel As Range, dterng As Range, dtecel As Range, bharr As Range, bhcel As Range
    
    Set wb = ThisWorkbook
    Set aws = wb.Sheets("Summary")
    Set eidtbl = aws.ListObjects("EmpSumTbl")
    Set eidarr = eidtbl.ListColumns("SIPL ID").DataBodyRange
    Set bharr = wb.Sheets("Variables").ListObjects("BnkHolTbl").DataBodyRange

    For Each eidv In eidarr
        all = 0: vac = 0: sic = 0: com = 0: bah = 0: obh = 0: wfh = 0
        For Each ws In wb.Worksheets
            If ws.Name Like "[A-Z][a-z][a-z]-##" Then
                For Each tbl In ws.ListObjects
                    If InStr(tbl.Name, "ShfTbl") Then
                        Set dterng = ws.Range(Range("DteRng").Value)
                        For Each bhcel In bharr
                            Set dtecel = dterng.Find(what:=DateValue(Format(bhcel.Value, "dd-mm-yyyy")), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
                            Debug.Print dtecel.Address '-------> THIS IS THE PLACE WHERE IT GIVES THE ERROR 91
                        Next bhcel
                    End If
                Next tbl
            End If
        Next ws
    Next eidv

End Sub

Open in new window

LVL 1
Vipin KumarSr. Network EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will have to check whether .Find returns a range object otherwise dtecel range will not be set and will raise the error if you try to print it's address.
See if this works for you...

For Each bhcel In bharr
    Set dtecel = dterng.Find(Format(bhcel.Value, "dd-mm-yyyy"), LookIn:=xlValues, lookat:=xlWhole)
    If Not dtecel Is Nothing Then
        Debug.Print dtecel.Address
    End If
Next bhcel

Open in new window

Vipin KumarSr. Network EngineerAuthor Commented:
@Neeraj,

It is not returning a range object. I have tried above solution still it doesnt work.
NorieAnalyst Assistant Commented:
What formula(s) are you using to calculate dates?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vipin KumarSr. Network EngineerAuthor Commented:
@Norie,

The formula i am using to calculate dates is as below.

This is the formula that i put in the first Cell i.e. D3
=DATE($K$1,MATCH($D$1,MnthTbl[#Data],0),1)-WEEKDAY(DATE($K$1,MATCH($D$1,MnthTbl[#Data],0),1),2)+1

After that all the cells for ex. cell E3 has below formula
=D3+1

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Can you upload a truncated version of your file after removing sensitive data if any?
Vipin KumarSr. Network EngineerAuthor Commented:
I have attached the truncated version of the file
Dummy-Sheet.xlsm
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
This works for me...

For Each bhcel In bharr
    Set dtecel = dterng.Find(What:=Format(bhcel.Value, "dd/mmm/yy"), After:=ws.Range("C3"), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not dtecel Is Nothing Then
        Debug.Print dtecel.Address
    End If
Next bhcel

Open in new window

Vipin KumarSr. Network EngineerAuthor Commented:
@Neeraj,

This doesn't work for me. My system short date is set to format dd-MMM-yy does that has anything to do with it.
Vipin KumarSr. Network EngineerAuthor Commented:
The / had to be escaped with \ to make it work.

For Each bhcel In bharr
    Set dtecel = dterng.Find(What:=Format(bhcel.Value, "dd\/mmm\/yy"), After:=ws.Range("C3"), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not dtecel Is Nothing Then
        Debug.Print dtecel.Address
    End If
Next bhcel

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.