We help IT Professionals succeed at work.

Runtime Error for finding dates

126 Views
Last Modified: 2018-12-28
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

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED 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
CERTIFIED EXPERT

Commented:
What formula(s) are you using to calculate dates?
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
CERTIFIED 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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.