Match error... but there should be a match

[Luke777]

Hi all,

I'm using the following code

Sub test3()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim promostartDateTime As Date
Dim SearchRow As Integer
Dim sTime As Double

Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)

promostartDateTime = Application.WorksheetFunction.Text(ws2.Range("A2"), "dd/mm/yyyy") & "  " & Application.WorksheetFunction.Text(ws2.Range("F2"), "hh:mm:ss") 'can ignore this line
sTime = CDate(promostartDateTime)
SearchRow = Application.Match(sTime, ws1.Range("A4:A148"), 0)

Debug.Print promostartDateTime; SearchRow; ws1.Range("A14").Value; sTime 'just looking to see what the values are

End Sub

debug.print (when commenting out SearchRow = to prevent error) shows: "24/01/2022 09:00:00 0 44585.375 44585.375"

This means that A14 in ws1 SHOULD match sTime? instead I'm given the debug type-mistmatch error 13 because it can't find a match.

Any ideas why it cannot match the value of sTime and A14 to give me the row number I'm looking for?

Thanks all

 

[Luke777]

okay so changing the match type from 0 to 1 works... but I do not understand why type 0 doesn't or if this might have consequences with inaccurate matches later down the line

 

[BSALV]

unless there is A2 and F2 aren't a date and a time the construction to change both to a string and reverse is unnecessary and leads to error, as VBA follows the american dateformat.
Better is, i think stime=Ws2.range("A2").value2+Ws2.range("F2").value2

promostartDateTime = Application.WorksheetFunction.Text(ws2.Range("A2"), "dd/mm/yyyy") & "  " & Application.WorksheetFunction.Text(ws2.Range("F2"), "hh:mm:ss")     'can ignore this line
sTime = CDate(promostartDateTime)
mydates = ws1.Range("A4:A148").Value2
SearchRow = Application.Match(sTime, mydates, 0)

 

[Luke777]

unless there is A2 and F2 aren't a date and a time the construction to change both to a string and reverse is unnecessary and leads to error, as VBA follows the american dateformat.
Better is, i think stime=Ws2.range("A2").value2+Ws2.range("F2").value2

promostartDateTime = Application.WorksheetFunction.Text(ws2.Range("A2"), "dd/mm/yyyy") & "  " & Application.WorksheetFunction.Text(ws2.Range("F2"), "hh:mm:ss")     'can ignore this line
sTime = CDate(promostartDateTime)
mydates = ws1.Range("A4:A148").Value2
SearchRow = Application.Match(sTime, mydates, 0)

I'm getting type mistmatch with this - which I'm guessing has something to do with me using the uk date format?

 

[Luke777]

unless there is A2 and F2 aren't a date and a time the construction to change both to a string and reverse is unnecessary and leads to error, as VBA follows the american dateformat.
Better is, i think stime=Ws2.range("A2").value2+Ws2.range("F2").value2

promostartDateTime = Application.WorksheetFunction.Text(ws2.Range("A2"), "dd/mm/yyyy") & "  " & Application.WorksheetFunction.Text(ws2.Range("F2"), "hh:mm:ss")     'can ignore this line
sTime = CDate(promostartDateTime)
mydates = ws1.Range("A4:A148").Value2
SearchRow = Application.Match(sTime, mydates, 0)

Just had another look - again works perfectly but only if the match_type is 1 instead of 0

 

[Luke777]

unless there is A2 and F2 aren't a date and a time the construction to change both to a string and reverse is unnecessary and leads to error, as VBA follows the american dateformat.
Better is, i think stime=Ws2.range("A2").value2+Ws2.range("F2").value2

promostartDateTime = Application.WorksheetFunction.Text(ws2.Range("A2"), "dd/mm/yyyy") & "  " & Application.WorksheetFunction.Text(ws2.Range("F2"), "hh:mm:ss")     'can ignore this line
sTime = CDate(promostartDateTime)
mydates = ws1.Range("A4:A148").Value2
SearchRow = Application.Match(sTime, mydates, 0)

Sorry, last follow up - whats the best data type for mydates? or should I leave it without a dim?

 

[BSALV]

variant, but unnecessary to declare.
What is the difference between a2+f2 and the match, something very small ? (1e-15???)

 

[Luke777]

variant, but unnecessary to declare.
What is the difference between a2+f2 and the match, something very small ? (1e-15???)

I cant see any difference at all... they're both date value 44585.375 according to debug.print. Cell formatting should make any difference should it? e.g. on screen ws1.Range("A4:A148") are formatted as hh:mm but the actual value remains the same (and contains the full date that is used it other bits of code elsewhere that works fine) otherwise it wouldn't match at all would it?