Wizard13
New member
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!
Steve
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 SubSteve