Avatar of Vipin Kumar
Vipin Kumar
Flag for India asked on

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

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Vipin Kumar

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

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 Kumar

ASKER
@Neeraj,

It is not returning a range object. I have tried above solution still it doesnt work.
Norie

What formula(s) are you using to calculate dates?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vipin Kumar

ASKER
@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)

Can you upload a truncated version of your file after removing sensitive data if any?
Vipin Kumar

ASKER
I have attached the truncated version of the file
Dummy-Sheet.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vipin Kumar

ASKER
@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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.