[dba-Tech] Excel Find problem

Hi Arthur

I recall something similar, and for that reason I created this function which you may be able to modify to fit your purpose.
Please note all the nice comments:

Function RLookup(ByVal rngLookup As Range, _
                 ByVal strField As String, _
                 Optional intSearchOrder As Integer = xlByRows, _
                 Optional intOffset As Integer = 1) As Variant

' Looks up a value in the named range rngLookup from the cell,
' offset intOffset rows below or columns right from the found cell.
'
' 2000-07-30.
' Cactus Data ApS, Gustav Brock

' Default intSearchOrder is xlByRows, as named ranges built this way
' are easily attached by Access.
  
  Dim rng       As Range
  Dim intRow    As Integer
  Dim intColumn As Integer
  
  ' No special errorhandling.
  On Error Resume Next

  If intSearchOrder = xlByRows Then
    ' Search by rows.
    ' Read from found row, offset intOffset rows, and found column.
  Else
    ' Search by columns.
    ' Read from found row and found column, offset intOffset columns.
    intSearchOrder = xlByColumns
  End If
  
  With rngLookup
    ' Start search from upper left cell in range by starting in lower right cell.
    ' Search case sensitive for whole words in values only.
    Set rng = .Find(strField, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, intSearchOrder, , True)
  End With
  If Not rng Is Nothing Then
    ' Searched value found.
    ' Lookup value to retrieve.
    With rng
      intRow = .Row - (intOffset * (intSearchOrder = xlByRows))
      intColumn = .Column - (intOffset * (intSearchOrder = xlByColumns))
      ' Return value, offset intOffset rows or columns from cell with found value.
      RLookup = .Worksheet.Cells(intRow, intColumn).Value
    End With
    Set rng = Nothing
  End If

End Function

/gustav

>>> fuller.artful at gmail.com 01-11-2007 14:51 >>>
I'm having trouble with Excel's find method. I know that the target data is
there, but the Find method keeps returning Nothing and I can't discern my
error. Here is the code:

<code>
                Dim intr As Integer
                Dim MatchedCell As Range
                With CntrySheet.Range("B1:B50")
                     Set MatchedCell = Cells.Find(What:="Asset Mix of
Underlying Funds:", _
                                                After:=ActiveCell, _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                SearchDirection:=xlNext, _
                                                MatchCase:=False, _
                                                SearchFormat:=False)

                    If Not MatchedCell Is Nothing Then
                        intr = MatchedCell.Row + 2
                    Else
' a band-aid until I figure out what's wrong with the search
                        intr = 18
                    End If
                End With
</code>


I read somewhere that Excel remembers your last Find settings, which is why
I specify them all, but it still doesn't work. I can see the target string,
it's right there on row 18, but I cannot guarantee that it will always be on
row 18. I have also substituted "walk the column" code for the above,
comparing each entry to the target string -- which works, but I would like
to know why my Find code is failing.

TIA,
Arthur