How to use .Find in VBA to find a specific data type?

0
votes

So I found this on the microsoft website (https://msdn.microsoft.com/en-us/library/office/ff839746.aspx) when I was trying to program something in VBA

expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) expression A variable that represents a Range object.

What: The data to search for. Can be a string or any Microsoft Excel data type.

And I wanted my code to find the first cell with the "Date" data type in a certain range, temp. 

Dim temp As Range, next_date As Range
temp = Range("A63:A70")
Set next_date = temp.Find(Date)
Debug.Print (next_date)

But I keep getting the "Object variable not set" error, which I think means it was unable to find a date in the range. There is definitely a date in the range, but when I mouse over the "Date" I typed in the .Find(), I realise it shows the date today.

I think this code might be trying to look for today's date in the range. But I just want it to find a cell with a generic "Date" data type, is there any way to do this without specifying a specific date? Thanks!!

3
vbaexcel
Thanks for all your answers! I realised that I misread "Can be a string or any Microsoft Excel data type" -- I thought it meant that .Find could be used to find a specific data type, but I now realise it meant .Find can be used to find a certain string or number or date or any other data of other excel data types. I hope to avoid a loop because I actually am dealing with many hundreds of rows (but didn't specify that in the example) but so I'll probably use your answer, Jeeped! Also thank you Alex P and Dawid for the helpful input – cobaltB12

3 Answers

0
votes

The question is not so much 'Is there a Date in A63:A70?' but 'What else is in A63:A70?'. Dates are not really a separate type of value. For most intents and purposes (more on this later), they are considered numbers. If all you want to to find the first Date-type in a range containing Dates, Text, Blanks but no other numbers then this should do.

    Dim temp As Range
    On Error Resume Next
    Set temp = Range("A63:A70").SpecialCells(xlCellTypeConstants, xlNumbers)
    If Not temp Is Nothing Then
        Set temp = temp.Cells(1, 1) 'reset temp to the 1st date if there are more than one
        Debug.Print temp.Address
    Else
        Debug.Print "no date in range"
    End If

The reason I said most intents and purposes is because VBA does have an IsDate Function. This likely looks at a) numeric nature of the value, b) difference between the Range.Value and Range.Value2 and c) the number format of the cell to determine whether a cell value is 42,149 or 25-May-2015. However, the IsDate function can only examine a single cell at a time so a time-consuming loop through the cells is necessary.

    Dim temp As Range
    For Each temp In Range("A63:A70")
        If IsDate(temp) Then Exit For
    Next temp
    If Not temp Is Nothing Then
        Debug.Print temp.Address
    Else
        Debug.Print "no date in range"
    End If

Your example was only 8 cells so a loop is not going to be overly detrimental to performance but it would certainly slow things down with a few thousand cells to individually examine.

2
votes

I am not sure you can use Find() to look for any value that is a date type. I think you would need to specify the actual date you are searching for instead. Example:

Set FoundCell = Range("A1:A10").Find (what:="7/18/1998")

An alternative option is a simple loop:

Sub FindNextDate()
    Dim val As Range

    For Each val In Range("A1:A10")
        If IsDate(val) Then
            Debug.Print "Date: " & val & " found at cell: " & val.Address
            Exit Sub
        End If
    Next val
End Sub
0
votes

temp is a object Range. You must use set -> What does the keyword Set actually do in VBA?

I think you cant find data type using .Find() but, you can try find format which indicates that we are dealing with date:

Sub tt()

Dim temp As Range, next_date As Range
Set temp = Range("A60:A70")

Application.FindFormat.NumberFormat = "m/d/yyyy"

Set next_date = temp.Find("", SearchFormat:=True)

Debug.Print next_date
Debug.Print next_date.Address

End Sub