0

I am trying to Find a specific string in a Range. Run-time error '91'keeps showing up. I understand this happens because the Find function does not find any coincidence. Nevertheless I am unsure why this is happening.

Currently I have a Listbox in a Userform. When I select an option in the Listbox, the string of such selection will be shown on a Userform Textbox (txt_selected). On a worksheet I have exactly the same list as displayed on the Listbox. I want to find the row in the worksheet where there is a coincidence with the selected element.

I have an if statement which is triggered when there is a match found (countif>0), displaying a MessageBox with the message "Match found". However, when I try to find the row number of the match I get the error.

My code is the following:

Private Sub lstbx_diarias_Click()

For n = 0 To (UserForm1.lstbx_diarias.ListCount - 1)
    UserForm1.txt_selected.Value = UserForm1.lstbx_diarias.Text
Next n

End Sub


Sub llenar_checklist()

Dim result_row As Long
Dim actividad As String
Dim look_range as Range

''' Actividades Diarias
If UserForm1.chbx_diarias.Value = True Then

    Set look_range = Sheets("Checklist").Range("C9:C23")

    actividad = UserForm1.txt_selected.Value

    If Application.WorksheetFunction.CountIf(look_range, actividad) > 0 Then

        MsgBox "Match found"
        result = look_range.Find(what:=actividad, LookIn:=xlValues, LookAt:=xlWhole)
        result_row = result.Row

    End If
    MsgBox "The row is " & result_row

End If

End Sub

According to VBA, the error comes from line where result if defined. Any ideas why this may be happening?

Thanks a lot in advance.

Diego Gc
  • 175
  • 2
  • 15
  • It runs fine for me when I eliminate references to your userform and use the string "qwerty" for actividad. – Tin Bum Oct 28 '20 at 19:08
  • Since `result` returns a range, I get an error when trying to set `result` without using `set`. But maybe I'm missing something. (Technically I get the error when trying to use calling `result.Row` in the next line) – Christofer Weber Oct 28 '20 at 19:12
  • `Dim result As Range`, then `Set result = ...` – BigBen Oct 28 '20 at 19:12
  • Actually, I tried using the "qwerty" string directly, as Tin Bum did. As I got errors as well, this helped me realize the mistake was I had some merged cells. I did not know the Find function does not work well on merged cells. Silly mistake. Thanks for all of your advice! – Diego Gc Oct 28 '20 at 19:16
  • Find should work on merged cells. – BigBen Oct 28 '20 at 19:39

0 Answers0