Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA code copied to new workbook throws runtime "error 91" - UPDATE - SOLVED

2

I wrote code for a "Worksheet_Change" event and everything works as intended in the workbook it was created in.

I then copied the code to the workbook and the worksheet I wanted to use it on and it throws the "runtime error 91" at this line:

OppRow = Range(Cells(6, Tcol - 4), Cells(37, Tcol - 4)).Find(OPP).Row

The variable "OppRow"  and "Tcol" are declared as Long. The variable "Tcol" is given a value (a number) and the variable "OPP" is  given a 4-character string. This happens before the problematic line.

This problematic line works in the testing workbook but not the workbook it was copied to. This is the only line giving me trouble. Any help will be appreciated.

Cheers

UPDATE - Jan. 3/26  -  SOLVED

All it took was modifying the "Find" portion from:

Find(OPP).Row

to this:

Find(OPP, LookIn:=xlValues, LookAt:=xlWhole).Row

Now it works as intended. But I can't explain why the testing workbook didn't need this tweak.

Cheers   :-)

excelmacros-vba
MS Office Version: MS Office 2007 2016
posted by: WillieD24 (rep: 723) on Fri Jan 2, 2026 at 10:29 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Willie

Apologies but I'm only checking the Forum occasionally now (given the low traffic for months). 

I suspect that the odd behaviour arises from an aspect mentioned under Remarks in the Microsoft guidance Range.Find method (Excel) (see bits in bold):

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.

Happy New Year! 

posted by: John_Ru (rep: 6792) on Tue Jan 6, 2026 at 1:48 pm
Discuss

Discussion

Hi John,

Thanks for pointing this out. I had read that, but didn't give it much thought since the line worked perfectly in my test workbook.
I had tried a few things but got frustrated when nothing solved my problem so I made my post. Not one to give up easily, I kept experimenting after posting. And low and behold I managed to figure it out (as noted in my post).

Happy New Year!

Cheers   :-)
WillieD24 (rep: 723) Jan 7, '26 at 1:35 pm
Hi Willie. I saw you'd solved it but was suggesting that the"saved method" might be different for a new workbook (if not saved with the template - I didn't check ). There was no need to select my Answer but thanks,
John_Ru (rep: 6792) Jan 7, '26 at 1:57 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login