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

[foible]

Hoping someone out there can help!

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!

 

[Scott Huish]

Hi, welcome to the board!

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

 

[foible]

It's text with the date in "MM/DD" format within it. Only when it's not in there - bingo error message.

 

[Scott Huish]

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:

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

 

[foible]

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]

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]

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!