Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Match the best way to go?
I'm trying to determine if a given date exists within a list of dates. The
best my limited skills could produce was to use the Match function and check for an error if the number wasn't in the list. Is there a better way? What I have looks like this: Sub Foo_TestMatch() Dim wks As Worksheet Dim rng As Range Dim x As Long, y As Double Set wks = ActiveSheet Set rng = wks.Range("A1:A10") x = 12 Err.Clear On Error Resume Next y = Application.WorksheetFunction.Match(x, rng, 0) If Err.Number < 0 Then MsgBox Err.Number Err.Clear On Error GoTo 0 End Sub (To test this, I was just using numbers, so that's why nothing is a date.) Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet.Match the best way to go?
Here is what I would probably do, I hard coded the testTxt, but you could use
a reference to a cell or use "testTxt=InputBox(Enter search value:)": Public Const TARGET_COL = "A" Public Const START_ROW = 1 Public Function Foo_TestMatch() as Boolean Dim testTxt As String Dim lastRow As Long Dim rowIndex As Long Dim matchFound As Boolean 'Enter test value here testTxt = "2/20/07" 'Grab last row in worksheet lastRow = Range(TARGET_COL & "65536").End(xlUp).Row 'Loop to find value matchFound = False rowIndex = START_ROW While Not matchFound And (rowIndex <= lastRow) If CDate(Range(TARGET_COL & rowIndex)) = CDate(testTxt) Then matchFound = True Else: rowIndex = rowIndex + 1 End If Wend 'Set function result Foo_TestMatch=matchfound 'You may also want to alert user If matchFound Then MsgBox "The text " & testTxt & " was found in row " & rowIndex & ".", vbExclamation Else: MsgBox "The text " & testTxt & " was not found.", vbExclamation End If End Function "Ed" wrote: I'm trying to determine if a given date exists within a list of dates. The best my limited skills could produce was to use the Match function and check for an error if the number wasn't in the list. Is there a better way? What I have looks like this: Sub Foo_TestMatch() Dim wks As Worksheet Dim rng As Range Dim x As Long, y As Double Set wks = ActiveSheet Set rng = wks.Range("A1:A10") x = 12 Err.Clear On Error Resume Next y = Application.WorksheetFunction.Match(x, rng, 0) If Err.Number < 0 Then MsgBox Err.Number Err.Clear On Error GoTo 0 End Sub (To test this, I was just using numbers, so that's why nothing is a date.) Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete value on worksheet 2 that match values on worksheet 1 | Excel Discussion (Misc queries) | |||
Match worksheet | Excel Discussion (Misc queries) | |||
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet | Excel Worksheet Functions | |||
Match WORKSHEET | Excel Worksheet Functions | |||
Match 2 worksheet | Excel Worksheet Functions |