Run time error '1004': Unable to get the find property

foible

New Member
Joined
Nov 17, 2005
Messages
4
Hoping someone out there can help!

Code:
comment = ActiveCell.Value
        
        comment = Application.WorksheetFunction.Find(Format(Date, "MM/DD"), comment)

When I try to run this and it can't find the text for the current date (ie. if you were in Excel rather than VBA it would return "N/A") it gives me the dreaded 1004 run time error message. If it can find the current date in the ActiveCell then there is no problem (which makes me wonder what the hell I'm doing wrong because it doesn't seem to be syntax problem, or else why does it work at all?).

If I do a IsNA error check to the above it gives the same result...

Many thanks in advance to anyone who can assist!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Is the value in the cell you're checking actually a date or is text?
 

foible

New Member
Joined
Nov 17, 2005
Messages
4
It's text with the date in "MM/DD" format within it. Only when it's not in there - bingo error message.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Since it looks like you are trying to find out where in the string the date starts, try this, it will return 0 if it is not there:

Code:
Sub test()
Dim comment As Long
comment = InStr(1, ActiveCell, Format(Now, "mm/dd"))
MsgBox comment
End Sub
 

foible

New Member
Joined
Nov 17, 2005
Messages
4

ADVERTISEMENT

Thanks for that. Unfortunately doesn't solve my problem - there will always be other text besides the date in the ActiveCell (which is why I was using find in the first place).

A bit of background to explain what I'm trying to do here:

The active cell in this case is for the user to enter comments (via a UserForm). Everytime they enter comments, the text they enter is preceded by a date stamp: Format(Date, "mm/dd"). What I'm trying to let the user do is replace any comments they made today (hence the test to find the current date) but not allow them to edit comments entered on previous days, in order to preserve the audit trail.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
My code will check for the current date in the format mm/dd in the activecell even if there is other text in it. It is just using a native VBA function. If the value is greater than 0, it found the text.
 

foible

New Member
Joined
Nov 17, 2005
Messages
4
Sorry - I thought you'd posted something else, but next time I checked it wasn't there.

Fantastic - works a treat. Thanks so much for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top