Runtime error 9 in "How to find a date in a range with VBA ?"
 

Dim lngRow As Long
Dim dteInput As Date
'Range has several dates formatted as "ddd, dd/mm/aa"
Range("G3:G10").Select

'dteInput is being copied from a cell that has "23/11/2004" as a date value
(format cell = date)
'I use Windows XP, Excel XP with Spanish settings!

With Selection
'I get a runtime error 9 here

lngRow = Cells.Find(What:=dteInput, After:=ActiveCell, LookIn:=xlFormula,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Row

MsgBox "lngRowDteImport = " & lngRowDteImport, vbInformation

End With

If lngRowDteImport 0 Then 'Import Fecha already exists in Range G3:G10!

MsgBox "Date " & CStr(dteImport) & exists", vbInformation, "Terminated"
Exit Sub

Any idea why this gives an error?

TIA
Martin

Runtime error 9 in "How to find a date in a range with VBA ?"
 

One possible reason is if the sheet is protected or hidden then you will have
a problem.

"Martin Los" wrote:

Dim lngRow As Long
Dim dteInput As Date
'Range has several dates formatted as "ddd, dd/mm/aa"
Range("G3:G10").Select

'dteInput is being copied from a cell that has "23/11/2004" as a date value
(format cell = date)
'I use Windows XP, Excel XP with Spanish settings!

With Selection
'I get a runtime error 9 here

lngRow = Cells.Find(What:=dteInput, After:=ActiveCell, LookIn:=xlFormula,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Row

MsgBox "lngRowDteImport = " & lngRowDteImport, vbInformation

End With

If lngRowDteImport 0 Then 'Import Fecha already exists in Range G3:G10!

MsgBox "Date " & CStr(dteImport) & exists", vbInformation, "Terminated"
Exit Sub

Any idea why this gives an error?

TIA
Martin