Find function on dates


New member
Oct 19, 2012
Reaction score
God's Own County
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, _
  If Not rngCell Is Nothing Then
    Call MsgBox("Searching for " & varSearchFor & vbCrLf & _
                "rngCell = " & rngCell & vbCrLf & _
                "Address = " & rngCell.Address, _
                vbInformation, _
    Call MsgBox("Cannot find " & varSearchFor & vbCrLf & _
                "in the range " & strRange, _
                vbCritical, _
  End If
End Sub