Problem with "Find"

[Mackeral]

I am looking in a column by searching for a partial matche. The problem iwth this code is that it skips over every second find.
The problem is with the "Rng" variable:: I increase it by 1 column, but if the next row contains what should be findable, it doesn't find it.
And it use to work a few days ago. I also notice that I have a terrible time working on weekends because I'm guessing that
they are trying out a test version code then. And it works again on Monday.


Code

Sub Find_Test()
   
    Rng = "G4:G455"
   Look_For = "Computer"
   Look_At = x{Part
   Case_Arg = False

    ' Lookup a String in a Sheet and return its address in ROW and COL.

    Found = ""
    If InStr(Rng, ":") = 0 Then Rng = Rng & ":" & Rng
   
    Do
        With ACCOUNTS.Range(Rng)
            Set Hold = .Find(What:=Look_Up, _
                            LookIn:=xlValues, _
                            LookAt:=Look_At, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=Case_Arg)
        End With
   
        If Hold Is Nothing Then

            Debug.Print Line
            Stop

        Else
            ' Extra test to be sure
            Ans_Row = Hold.Row
            Ans_Col = Hold.Column
           
            Rng = Make_Range(Ans_Row + 1, Ans_Col, -1, Ans_Col)
            Line = Line & vbCr & Hold & ", " & Ans_Row
        End If
    Loop
   
       
End Function ' Find_String

The following are sequntial cells in the seadhed column:

Communications:Computer Computer: Computer:Web Computer:Non-Taxable Computer:Taxable Computer:Software Computer:Hardware Computer:Internet Computer:Service Computer:Supplies [/CODE] This is the output of the program with the fiound line numbers: Communications:Computer, 117 Computer:Web, 119 Computer:Taxable, 121 Computer:Hardware, 123 Computer:Service, 125 Computer:Supplies, 126

 

[Mackeral]

Putting this here was a mistake.

The problem is not a programming problem, but rather than something going wrong with VBA Find,
and this needs to be reported to Microsoft, which I will be doing.

 

[Scott Huish]

I don't have your Make_Range function to test it, but there is a programming error for sure.

This line should read like this:

Look_At = xlPart

not

Look_At = x{Part

 

[Fluff]

In addition, on this line

Set Hold = .Find(What:=Look_Up, _

Look_Up does not have a value, it should probably be Look_For

 

[JEC]

Also, a sub should end with End Sub instead of End Function

 

[MARK858]

Should also define the After:= parameter, something like

After:=Range(rng).Cells(Range(rng).Rows.Count)

 

[Peter_SSs]

Apart from all the issues mentioned above, I would also recommend declaring all variables.

The problem is not a programming problem, but rather than something going wrong with VBA Find,
and this needs to be reported to Microsoft, which I will be doing.

I think that this statement is incorrect & it is a programming problem.
According to the documentation for the Find method the 'After' argument works like this (my emphasis) and I think that is exactly what it is doing for you.

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface.

Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell.

If you don't specify this argument, the search starts after the cell in the upper-left corner of the range.

As mentioned above, we do not have your 'Make_Range' function but from the arguments being passed to that function it appears that you are setting the new Rng to start one row below the currently found 'Hold' cell. So when "Computer" is found in G117 I think that you are setting the new Rng to be G118:G455 so the second 'Find' looks for "Computer" after G118 and correctly finds it first in G119
after which Rng is set to G120:G455 so the third 'Find' looks for "Computer" after G120 and correctly finds it first in G121 etc.

We also have not been given your expected results, but I am assuming (=guessing) that it is this, or something pretty similar.

If that is what you are after, then you could try this method instead.

Sub Find_Test_v2()
  Dim Accounts As Worksheet
  Dim Rng As String, FirstAddr As String, Look_For As String, Line As String
  Dim LookupRng As Range, Hold As Range
  Dim Look_At As Long
  Dim Case_Arg As Boolean
 
  Set Accounts = ActiveSheet '<- Adjust if required
  Rng = "G4:G455"
  Look_For = "Computer"
  Look_At = xlPart
  Case_Arg = False
  Set LookupRng = Accounts.Range(Rng)
  With LookupRng
    Set Hold = .Find(What:=Look_For, _
                            LookIn:=xlValues, _
                            LookAt:=Look_At, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=Case_Arg)
    If Not Hold Is Nothing Then
      FirstAddr = Hold.Address
      Do
        Line = Line & vbCr & Hold.Value & ", " & Hold.Row
        Set Hold = .Find(What:=Look_For, _
                            After:=Hold, _
                            LookIn:=xlValues, _
                            LookAt:=Look_At, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=Case_Arg)
      Loop Until Hold.Address = FirstAddr
      Debug.Print Mid(Line, 2)
    End If
  End With
End Sub