VBA FIND to find a date

[Nurzhan]

Hallo,
With the code below I want to find a certain date in column A cell values. And then resize it to copy the new range to another place. Cells in column A are linked to another sheet. What is wrong here?

Sub example()
Dim rgFound As Range
Dim search As String

Set rgFound = Range("A1:A100").Find("09.06.18 1:00", LookIn:=xlValues)

rgFound.Resize(24, 16).Copy Destination:=Range("A1")    <<===Error "91"

End Sub

 

[JLGWhiz]

Dates can be a real pain to work with because what you see is not necessarily what Excel sees.

Try this syntax.

Set rgFound = Range("A1:A100").Find(#09.06.18 1:00#, LookIn:=xlValues)

The (#) symbol tells Excel that you are looking for a date literal. Remember dates are stored as a long integer or double and not as the format you are using. The exception is if you have entered a date as text.

This link might help you to understand better.
https://www.stl-training.co.uk/article-832-how-are-dates-stored-in-excel.html

 

[Nurzhan]

VBA doesn't allow putting it without quotes. If put in quotes same error appears. I tried to use a simple text and it's ok but problem appears when I use a date. I even tried to use numeric value of the date - no success. When I use Excel's find option to search for a date, it finds it well.

 

[Leith Ross]

Hello Nurzhan,

If the Range function fails then you will receive error 91 "Object variable or With block variable not set". You should always check the result of the find operation. This will prevent an error during execution.

Several of the Find arguments can be set or cleared by other system Find operations. In VBA you should always explicitly set all of the arguments before executing the Find operation.

Sub example()


    Dim rgFound As Range
    Dim search As String


        Set rgFound = Range("A1:A100").Find("09.06.18 1:00", ,xlValues, xlWhole, xlByRows, xlNext, False, False, False)


        If rgFound Is Nothing Then
            MsgBox "The date """ & "09.06.18 1:00" & """ was Not Found.", vbOKOnly+vbexclamation
            Exit Sub
        End If


        rgFound.Resize(24, 16).Copy Destination:=Range("A1")    <<===Error "91"


End Sub

 

[Nurzhan]

Hallo Leith Ross! Thanks but it doesn't work either)). But that date exists for sure.