Problems with "Find" looking for dates"

GrumpyOldGit

New Member
Joined
Jun 28, 2012
Messages
13
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!

Code:
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
 

GrumpyOldGit

New Member
Joined
Jun 28, 2012
Messages
13
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.
Code:
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
 

Forum statistics

Threads
1,081,901
Messages
5,361,937
Members
400,666
Latest member
UDLearning

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top