Cannot use Find on another worksheet

[7over6]

I have a workbook with two sheets. I have one macro that saves the text of the active cell in sheet 1 for later use, the macro then switches to sheet 2 and using the find function I want it to search for the variable I just setup but I keep getting run-time error 91.

Here is what I have:

Please Login or Register  to view this content.

I get the run-time error 91 with the Find lines of code yellow and the arrow pointing at the last row. I've tried all kinds of variations (with and without Activate) of the find function and as long as it is searching in sheet2 for the variable that was made in sheet1 it will NOT work. If I do this same search on Sheet1 I get no error. Also if I change the string name in Find to the same exact text just with quotes on it it will find that text without problem in Sheet2. But if I use a string I get an error. So frusterating! I'm not even doing anything that complex.
Once I get this to work I want to save the address where the string is found in Sheet2 and use that address later as well so some advice on how to get an address from a Find result would be great as well.
Can someone help?

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[6StringJazzer]

I am not 100% sure what your problem is but it is probably related to depending on ActiveCell and ActiveSheet to refer to things. Possibly ActiveCell is referring to a cell on Sheet1 when you call Find. It's better to be explicit rather than depending on what's Active, unless for user interface reasons you really need to activate something. Try this, but I can't test it without your file.

I added the red code, which will capture the cell that is found. Please review the blue code to determine what cell you really mean, because you have not yet activated a cell on Sheets(2) yet. I suspect you can omit the After argument completely.

Please Login or Register  to view this content.

[7over6]

Thank you for your help. I am still getting the same error with Find code all yellow and the arrow pointing a the last line. Maybe it doesn't like the .Activate part?
I do need it to initially pull the Terminal # from my own selection in Sheet 1 as I have not tackled the looping function yet (the task I am accomplishing ultimately needs to be repeated for about 400 rows which is why I am writing this script). If the Active cell is stores first and then the script activates some other cell the string that was stored is untouched and not changed correct?
I tried changing the string reference in the Find function to actual "" text which IS present and I still got the error.
Lastly - for the blue ActiveCell part - I am only needing to search through column A in sheet2 so what can I put in the After spot for that?

[ImStevenB]

Hi 7over6,

I worked with your existing code and made a couple of changes, but I got it to run

Please Login or Register  to view this content.

Basically, I broke up the Find a little. First I used a With statement and declared the Range instead of using Cells. You can reset that to look in a specific column if you are keeping terminal references in the same column.

I also took out the other optional declarations on the Find statement .. took it down to basics.

But any terminal reference I put in sheet(1) was found on sheet (2).

Hope this helps.

[7over6]

Nope, same old error! Thank you thought for trying to help. I used your exact code. I wondered if ActiveCell in Find on sheet2 was causing trouble so I clicks on a very early cell in A column so that it would search After that and find the TerminalStr but no luck still.

Attached is the sheet. What I'm trying to do is take the address cells from sheet 2 for each terminal ID (store) and move them to sheet1 and place them with the correct terminal ID. My find function is just to get an address for the Terminal ID in sheet2 and use that with the offsets to make strings for each address part which will then be moves to sheet1 using the location of the very first active cell (Terminal ID) and offsets to place in the right fields.

[ImStevenB]

Out of curiosity .. Do you have TerminalRng and TerminalStr declared earlier in the code?

I just assumed you did and Dim'd TerminalRng as Range and TerminalStr as String.

[7over6]

Ah no I forgot to do that. I added the Dims and declared rng as Range and the str as String but the rng one could not be set to ActiveCell.Address I thought an address and range were the same? So I changed Terminalrng to String. Still same error with find. WHY YOU NO READ STRING!

[7over6]

Ok... the problem is solved and I'm ashamed. The column of terminal ID's that Find was searching through all had spaces at the end thanks to our Access's database or whoever originall entered them. SO it was not finding the string because the string had spaces and sheet2's terminal ID's did not have spaces.

So I guess I'm smarter then I thought and dumber all at the same time.

[ImStevenB]

I kept looking through the code and trying to figure out why it was dying .. that makes sense. I see the space on the ToAccess sheet now.

Glad you got it solved.

Have a good day.

Don't forget to mark the thread Solved.