VBA Search within a given range

[CodyMartin]

Hello, thank you for looking.

I have the following code:

Please Login or Register  to view this content.

.... Other States in Code with basically the same code, just differing range values.


After which there are more ElseIf statements that are dependent on the state. My problem is this; each county is not unique to every state and rather than going through and modifying every single non-unique county name, I'd rather just search through a given range of values... which I thought I did (i.e. rngLook) which I update for each ElseIf. County(n) is a loaded array as string, as well as Area(n) and RR(n). County(n) should be the only variable effecting the search. However, when I run the code it just grabs the last county of that name on my spreadsheet rather then getting only the county name within the given range. What am I doing wrong?

Thank you,

Cody

[shg]

Welcome to the forum, Cody.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with Code Tags.

[StephenR]

If you just want to search within rngLook you need to amend to:

Please Login or Register  to view this content.

The full stop before Find references rngLook.

[bhofsetz]

In your Set rngFound line you are having the code look at all the cells on the sheet not just those in rngLook

try something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

HTH

[shg]

Cody,

Thanks for the code tags.

bhofsetz,

The syntax in both of your statements is wrong; try it.

Stephen,

Yours is fine (as you know), but doesn't make clear that After needs to be a cell in the Find range, and you should always specify LookIn and SearchOrder (in addition to MatchByte) lest they default to prior values:

Please Login or Register  to view this content.

[StephenR]

Thank you shg. Remiss of me, I saw one error and stopped looking.

[CodyMartin]

Thank you very much shg, bhofsetz, and StepherR, my problem is now solved! This portion of my program works perfectly (and much more efficiently). I appreciate the help.

Regards,

Cody

[shg]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save