Link to home
Avatar of Vipin Kumar
Vipin KumarFlag 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

Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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

Avatar of Vipin Kumar

ASKER

@Neeraj,

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

What formula(s) are you using to calculate dates?
@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

Can you upload a truncated version of your file after removing sensitive data if any?
I have attached the truncated version of the file
Dummy-Sheet.xlsm
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
@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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.