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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,055
Messages
5,835,157
Members
430,342
Latest member
Sailingexcel

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
Top