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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi, welcome to the board!

Is the value in the cell you're checking actually a date or is text?
 
Upvote 0
It's text with the date in "MM/DD" format within it. Only when it's not in there - bingo error message.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,214,912
Messages
6,122,204
Members
449,072
Latest member
DW Draft

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top