Find, then copy and paste macro not working

**ufopilot** · 03-12-2014, 01:24 PM

I have used this macro so many times in the past, and it is used throughout this workbook to collect all kinds of information.
But it seems to choke on this little simple task!
I am trying to overwrite the oldest of two entries in two rows with a fresh row of data...
Am I missing something?
I have posted a section of my project, with the cells in the same address as the original to match the macro
The macro is in a textbox under the cells in question
Thank you for taking a look.

**Leith Ross** · 03-12-2014, 02:11 PM

Hello ufopilot,

You have made a common mistake when using the Find method for a Range. Because the Find method used for a Range object is the same structure as the Windows Find/Replace dialog, many of the values are retained after they have been used. This is one instance where VBA does not clear all the variables after the Sub or Function ends. Get into the habit of setting these variables when you use the Find method and you will eliminate a lot of problems.

From the VBA Help Files
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Corrected Code is in Blue

Please Login or Register  to view this content.

**ufopilot** · 03-13-2014, 05:14 AM

Hi Leith,
Thank you for your reply. I read the information you provided, but have been unsuccessful in solving my problem.
Then,I have tried the easy way - copying the whole code you replied with, and pasting it over the original.
The code hung up at the same area in question. I changed the "Find" to the actual value of the formula from "M85" to "x", but still the same result...the code hangs up at the same place.

**ufopilot** · 03-13-2014, 06:46 AM

Keith,
I noticed that my formula was not working as expected. This may have had something to do with the military time format I am using.
Instead of a formula in M85, I changed the formula K88:K89, I to: IF(AY88=MIN($AY$88:$AY$89),"x",""), which gives me the the value("x" in M85 directly.
I placed the original macro back in, and all is well. But you gave me more food for thought, and this can only be a benefit to me. Thank you for your help.