Problems with "Find" looking for dates"

[GrumpyOldGit]

I'm trying to develop a function to locate a date in a specific column in a spreadsheet (Excel 2010).
I've tried lots of variations but still get "Nothing" as a Find result.
The Worksheet (Sheet1) has column "B" formatted as "Date". In B2 I have "=NOW()". In B3 to B30 I have "=Bx+1", so each cell holds the next day's date.
The Worksheet (Sheet1) has column "C" formatted as "General". In C2 I have 1. In C3 to C30 I have "=Cx+1", so each cell holds the next number.

In the following code which calls the Locator subroutine to find a Number, then a Date, the Number search works, but the Date search fails.

I've tried all sorts of methods for matching the formats of what I'm searching for to what I'm looking at, but to no avail.

Can anyone help me to get this code working? (I think I'm getting the same problems with different "location" routines (e.g. "Application.Worksheetfunction.Match") which gives me an "Unable to get the Match property of the Worksheetfunction class" error.)

Any help would be gratefully appreciate by a head sore from repeated bashings on a brick wall!

Option Explicit
Sub Tester()
Dim varSearchFor                As Variant
  varSearchFor = 30
  Call Locator("C", varSearchFor)
  varSearchFor = CLng(DateValue("17/01/2013"))
  Call Locator("B", varSearchFor)
End Sub
Sub Locator(strColLetter As String, _
            varSearchFor As Variant, _
            Optional strSheetName As String = "Sheet1")
Dim rngSearch                   As Range
Dim varResult                   As Variant
Dim strSearchFor                As String
Dim shtSheet                    As Worksheet
Dim lngLastRow                  As Long
Dim rngCell                     As Range
Dim strRange                    As String
Dim lngSearchFor                As Long
'*
'** Get the range of used cells for the supplied column.
'** We assume we're dealing with "Sheet1".
'*
  Set shtSheet = Sheets(strSheetName)
  lngLastRow = shtSheet.Range(strColLetter & Rows.Count).End(xlUp).Row
  
  strRange = strColLetter & "2:" & _
             strColLetter & lngLastRow
  Set rngCell = shtSheet.Range(strRange).Find(What:=varSearchFor, _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
  If Not rngCell Is Nothing Then
    Call MsgBox("Searching for " & varSearchFor & vbCrLf & _
                "rngCell = " & rngCell & vbCrLf & _
                "Address = " & rngCell.Address, _
                vbInformation, _
                "DIAGNOSTIC")
   Else
    Call MsgBox("Cannot find " & varSearchFor & vbCrLf & _
                "in the range " & strRange, _
                vbCritical, _
                "DIAGNOSTIC")
  End If
End Sub

Steve

 

[Andrew Poulsom]

This may help you:

VBA Procedures For Dates And Times

 

[GrumpyOldGit]

I HATE YOU ANDREW!!!

I've spent two days using different code to (not) achieve my aim, and in under 15 minutes you've solved it. And I thought I was pretty good!

Seriously, and sincerely, thanks a lot. For anyone too idle to follow your link, and in case it ever gets broken, I've included the working code below.

Option Explicit
Sub Tester()
Dim varSearchFor                As Variant
  varSearchFor = 30
  Call Locator("C", varSearchFor)
  varSearchFor = "17/01/2013"
  Call DateLocator("B", varSearchFor)
End Sub
Sub Locator(strColLetter As String, _
            varSearchFor As Variant, _
            Optional strSheetName As String = "Sheet1")
Dim rngSearch                   As Range
Dim varResult                   As Variant
Dim strSearchFor                As String
Dim shtSheet                    As Worksheet
Dim lngLastRow                  As Long
Dim rngCell                     As Range
Dim strRange                    As String
Dim lngSearchFor                As Long
'*
'** Get the range of used cells for the supplied column.
'** We assume we're dealing with "Sheet1".
'*
  Set shtSheet = Sheets(strSheetName)
  lngLastRow = shtSheet.Range(strColLetter & Rows.Count).End(xlUp).Row
  
  strRange = strColLetter & "2:" & _
             strColLetter & lngLastRow
  Set rngCell = shtSheet.Range(strRange).Find(What:=varSearchFor, _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
  If Not rngCell Is Nothing Then
    Call MsgBox("Searching for " & varSearchFor & vbCrLf & _
                "rngCell = " & rngCell & vbCrLf & _
                "Address = " & rngCell.Address, _
                vbInformation, _
                "DIAGNOSTIC")
   Else
    Call MsgBox("Cannot find " & varSearchFor & vbCrLf & _
                "in the range " & strRange, _
                vbCritical, _
                "DIAGNOSTIC")
  End If
End Sub
Sub DateLocator(strColLetter As String, _
                varSearchFor As Variant, _
                Optional strSheetName As String = "Sheet1")
Dim rngSearch                   As Range
Dim varResult                   As Variant
Dim strSearchFor                As String
Dim shtSheet                    As Worksheet
Dim lngLastRow                  As Long
Dim rngCell                     As Range
Dim strRange                    As String
Dim lngSearchFor                As Long
'*
'** Get the range of used cells for the supplied column.
'** We assume we're dealing with "Sheet1".
'*
  Set shtSheet = Sheets(strSheetName)
  lngLastRow = shtSheet.Range(strColLetter & Rows.Count).End(xlUp).Row
  
  strRange = strColLetter & "2:" & _
             strColLetter & lngLastRow
  Set rngCell = shtSheet.Range(strRange).Find(What:=DateValue(varSearchFor), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
  If Not rngCell Is Nothing Then
    Call MsgBox("Searching for " & varSearchFor & vbCrLf & _
                "rngCell = " & rngCell & vbCrLf & _
                "Address = " & rngCell.Address, _
                vbInformation, _
                "DIAGNOSTIC")
   Else
    Call MsgBox("Cannot find " & varSearchFor & vbCrLf & _
                "in the range " & strRange, _
                vbCritical, _
                "DIAGNOSTIC")
  End If
End Sub