Second Find is returning Type Mismatch "Run time error 13"

[jprlimey]

The first Find below is returning a correct value, however the second Find is returning type mismatch "Run Time error 13"
Any pointers as to what I'm doing wrong?

Please Login or Register  to view this content.

Ultimately I will be looping thru column B in the ws2 worksheet and for each match of the cell.value from wssheet worksheet i'll do some further processing

[JLGWhiz]

Might be this

Please Login or Register  to view this content.

Should be a colon separating the Bs.

[jprlimey]

The code does have a colon, I just mistyped in the post

Please Login or Register  to view this content.

[Richard Buttrey]

What is the '.....do something....' code doing?

Far to many people resort to loops to do stuff that is more efficiently done with filtering.

[jprlimey]

The ..... do something........
Are additional tasks that I will perform on the row that the second Find matches

[jindon]

1) Your 2 sets of With/End with are there for nothing.

2) Where did you set the reference to Rng1 in your code?

Please Login or Register  to view this content.

[jprlimey]

Opps another mistype on my part

Please Login or Register  to view this content.

The mistypes were my efforts to clean up the code before posting

[JLGWhiz]

Please Login or Register  to view this content.

This is the culprit. Change to

Please Login or Register  to view this content.

[jprlimey]

I’m assuming the (Rows.count, 1), is referring to column 1, where clearly I need column 2. Or is this just a wild guess on my part?

[JLGWhiz]

You are correct. Since you are querrying column 2 it will give the type mismatch if you try to start in column 1.
You can omit that parameter in the Find statement and let the default handle it unless you specifically want it to start at a certain cell when it begins the search.

[jprlimey]

JLGWhiz
Thanks for you input it did help solve the problem, however it also revealed another issue in the second find.

On the ws2 worksheet where we perform the 2nd Find routine we look for the cell.value returned by the 1st find routine which comes from wssheet worksheet. Now with the 2nd Find routine I want to collect information from adjacent cells on the same row where the 2nd Find locates a matching value. Then I planned on looking for the next matching value and again collect information from adjacent cells and repeat the Find etc. until no more matching records are found.

To collected the information from adjacent cells I planned on simply using an offset from current cell, but it does appear to work.

For example let's say the Apple was returned for the 1st Find, then we would Find the first occurrence of Apple in the 2nd Find, grab info from adjacent cells, then continue with the 2nd Find for next matching Apple and again grab info from adjacent cells, this would continue until all Apples have been processed at which time we would revert back to the 1st Find and process the next records for argument sake we will call that Pear. To aid with processing speed I did sort the dataset so that all Apples would be in consecutive rows.

Please Login or Register  to view this content.

[JLGWhiz]

If your first issue was resolved, then mark the thread as solved and start a new thread with the second issue, per forum guidelines. You probably wiil need to use the FindNext function to do what you want.
Regards, JLG