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

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

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

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

Sub x()
  
Dim rFind As Range, someValue As String
 
someValue = "someValue"

With ActiveSheet.Columns(1)
    Set rFind = .Find(What:=someValue, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        MsgBox "The value is " & rFind & _
           "The adjacent value is " & rFind.Offset(, 1)
    End If
End With
     
End Sub

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

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

If Not rFind is Nothing then

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.