Find value and set row number equal to variable

[mpearson]

Hello,
I've got two sheets of data that im trying to reference together without taking too much time. The autofilter then create a new sheet method works but is way too slow for looping through thousands of lines. What I would like to do is (for each iteration of the loop) is set a cell value = to a variable. Lets say A. Then find A in another sheet. Once I find A in the other sheet I would like to pass the row number of the cell that A was found in to another variable, lets say B. I'm getting runtime error 91. I don't understand as the object is defined as a range at the beginning of the code. Any help is greatly appreciated. Thank you.

Please Login or Register  to view this content.

The code above is only a portion of the actual code.

[stnkynts]

While this isn't going to answer your question directly hopefully it will point you in a good direction. I think this is what you are trying to accomplish. I ran this on a test workbook with 0 errors.

Please Login or Register  to view this content.

[mpearson]

Although that method eliminates the object error, it is giving me back the same row number each time. I stepped through the code and it appears as though the find function doesn't always work. I'm a little confused by the inconsistency.

[stnkynts]

Can you attach your workbook?

[mpearson]

Unfortunately I cannot. I have a feeling the problem is stemming from the loop. The method works through the first loop when I step through but once it moves to the second iteration the icell value stays the same despite a different A value. To give you a better idea of what I'm attempting I can try to lay out my process in a few steps.
*All of this takes place within a FOR loop.
1. Read "A" from cells(i, 2)
2. Use A as the find criteria on another sheet.
3. Store the row number as a variable, say K
4. Use the row number variable "K" to reference a cell corresponding to that row.
ie: cells(k, 4) or cells(k, 5)
I'm not sure if an object is the way to go with this since the row index with change for every step in the loop. I feel as if once the object is defined it is not resetting each time the program loops. Is there any other method of achieving this? I'm sort of stuck on it. I appreciate your help. Thank you.

[stnkynts]

This is really challenging on my end since I only have part of the picture without the workbook. If you can't submit the workbook can you submit your whole code. There might be something going wrong there that you are not aware of.