Runtime Error for finding dates

Vipin Kumar
Vipin Kumar used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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 Engineer

Author

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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vipin KumarSr. Network Engineer

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Can you upload a truncated version of your file after removing sensitive data if any?
Vipin KumarSr. Network Engineer

Author

Commented:
I have attached the truncated version of the file
Dummy-Sheet.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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 Engineer

Author

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.
Sr. Network Engineer
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial