'Find' not working as expected (Excel vba).

**staggers47** · 01-18-2013, 10:47 AM

Hi,

I have a routine that should look down a range of several columns of around 10K rows each for 'SearchItem'.
It needs to be columns because the first column is most likely to contain the item being looked for.

Here's the code.

If Trim(SearchItem) <> "" Then
   With Sheets("Data").Range("StdEng")
      Set Rng = .Find(What:=FindString, _
                  After:=.Cells(.Cells.Count), _
                  LookIn:=xlValues, _
                  LookAt:=xlWhole, _
                  SearchOrder:=xlByColumns, _
                  SearchDirection:=xlNext, _
                  MatchCase:=False)
      If Not Rng Is Nothing Then
         i = Split(Rng.Address, "$")
         WhichRow = i(2)
         WhichCol = i(1)
         What = Rng.Value
         Found = True
      End If
   End With
End If

I've obviously done something wrong, because here's what happened.

It worked exactly as hoped. Once. And then it never worked again. After that first time it just kept finding cell F3,

which is part of the range, but has always been empty. Since it did work once, I assume that the reason it doesn't now is something to do with the settings for After, LookIn, LookAt, etc.

I really need this to work, and if anyone can put me right I'd be deeply grateful.

**JosephP** · 01-18-2013, 11:17 AM

what is FindString?

**111StepsAhead** · 01-18-2013, 11:34 AM

Try this code instead. You call the function from another routine as needed. You will have to change the worksheet "Your worksheet of data" to whatever sheet you want to search.

Sub main22()
    findItem "blue", "Your worksheet of data"
End Sub

Function findItem(SearchItem As String, wksName As String)
    Dim x As Long
    Dim y As Long
    Dim last_Row As Long
    Dim last_Column As Long
    Dim wksName As String
    
    'wksName = "Your worksheet of data"
    
    Worksheets(wksName).Activate
    last_Row = Cells.Find("*", Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
    last_Column = Cells.Find(what:="*", After:=[A1], Searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    For x = 1 To last_Column
        For y = last_Row To 1 Step -1
            If Cells(y, x).Value = SearchItem Then
                Cells(y, x).Activate
                MsgBox "Found item in column " & x & " row " & y
                Exit Function
            Else
                'do nothing
            End If
        Next y
    Next x
    MsgBox "Item not found", vbOKOnly
End Function

EDIT: Also, if you are searching for the same string of text everytime then of course you will find it in the same place. Hopefully that's not the issue here.

**staggers47** · 01-18-2013, 11:59 AM

'if you are searching for the same string of text every time'

Dammit. I hadn't noticed that. The code is doing the right thing, but the wrong variable name was there, and I didn't notice.

Sorry people, false alarm. On the bright side, at least it means I did get the code correct (almost).

Again, sorry about that.