using the .find method and getting adjacent cell data

**Ouka** · 05-15-2009, 01:26 PM

Hi all,

So it's time I learn to use the .Find method to make my code tighter.

Until now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):

Please Login or Register  to view this content.

This worked fine for small projects with only a couple hundred rows of records even if it continued to execute after finding the desired data. But now I'm working with some datasheets with 20 thousand+ records. Looping like this is no longer an option.

So I'm learning to use the .Find method. I've figured out how to get it to work for finding the data I've indicated, but I have no idea how to set the foundAdjacentData value using the .Find method in the following snipet. Is it even possible?

Please Login or Register  to view this content.

Also, I can't use the .select method to select the found cell (and the report on the address) because the application is using a selected cell from another worksheet to set the "someValue" variable value in this and other modules.

Thanks in advance,

-o

**StephenR** · 05-15-2009, 01:38 PM

This will return the value of the cell to the right of the found cell:

Please Login or Register  to view this content.

**Ouka** · 05-15-2009, 01:56 PM

Thank you very much! I tweaked what you gave me and made it work in my app.

Please Login or Register  to view this content.

line is new to me. what is this saying exactly? Double negatives make head hurt =P

**StephenR** · 05-15-2009, 02:03 PM

Good.

If the search value is not found then the range variable (rFind) is set to nothing and subsequent code will result in an error. So you proceed only on the proviso that something is found, i.e. it is not nothing.