using the .find method and getting adjacent cell data

[Ouka]

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):

Dim lrow as integer
dim x as integer
dim someValue as integer

  lrow = activesheet.cells(rows.count, 1).end(xlup).row

For x = 1 to lrow
  If activesheet.cells(x, 1).value = someValue then
    msgbox "The value is " & Activesheet.cells(x, 1).value & _
               "The adjacent value is " & activesheet.cells(x + 1, 1).value
  End if
Next x

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?

Dim someValue as integer
Dim foundValue as string
Dim foundAdjacentData as string

With Activesheet.Coulmns(1)
  foundValue = .Find(What:=someValue)
End with

msgbox "The value is " & foundValue & _
           "The adjacent value is " & foundAdjacentData

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