Error 2042 - Returning row information

[cjh28]

Morning,

Hope someone can help me out a bit here,

I have workbook with 2 worksheets in it, using vlookup the first spreadsheet pulls information from sheet2 relating to elements in the first worksheet

Currently I have a userform that finds any instance of the user inputted search and tells you which sheet its in and allows u link to them. I also have a number of text boxes that I want populated with the information found on only the activesheet, not both. Currently I get a type mismatch error, and whilst it populates the information in debug mode I also get a Error 2042 on the following code:

TextBox5.Text = Cells(rngFind.Row, 2)

My full code looks like this:

Option Explicit

Sub locate(Name As String, Data As Range)

    Dim rngFind As Range
    Dim strFirstFind As String
    Dim Find As String
     
    With Data
        Set rngFind = ActiveWorkbook.Sheets(1).Cells.Find(Name, LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
                       
            Do
                If rngFind.Row > 1 Then
                    ListBox1.AddItem rngFind.Value
                    ListBox1.List(ListBox1.ListCount - 1, 1) = Data.Parent.Name
                    ListBox1.List(ListBox1.ListCount - 1, 2) = Data.Parent.Name & "!" & rngFind.Address
                     
                 
                    'Find = rngFind.Cells
                    'Location
                   
                    TextBox5.Text = Cells(rngFind.Row, 2)
                   
                    'Speed
                    TextBox10.Text = Cells(rngFind.Row, 6)
                    'TextBox9.Text = Cells(rngFind.Row, 5)
                    'Suitability
                    TextBox7.Text = Cells(rngFind.Row, 4)
                    'IP Range
                    TextBox8.Text = Cells(rngFind.Row, 8)
                                         
                    End If
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
    
End Sub

Private Sub CommandButton1_Click()

    Dim shtSearch As Worksheet
    
       
    ListBox1.Clear
    For Each shtSearch In ThisWorkbook.Worksheets
       
    
        locate TextBox1.Text, shtSearch.Range("A:M")
       
    Next
    If ListBox1.ListCount = 0 Then
        ListBox1.AddItem "No Match Found"
        ListBox1.List(0, 1) = ""
        ListBox1.List(0, 2) = ""
        'ListBox1.List(0, 3) = ""
     
        
    End If
End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim strSheet As String
    Dim strAddress As String
   
    strSheet = ListBox1.List(ListBox1.ListIndex, 1)
    strAddress = ListBox1.List(ListBox1.ListIndex, 2)
   
    If strAddress <> "" Then
        Worksheets(strSheet).Activate
        Range(strAddress).Activate
        
    End If
End Sub


Private Sub UserForm_Click()

End Sub

Any help would be greatly appreciated

Thanks,
Chris

 

[Andrew Poulsom]

Then you need to do 2 finds, one on Data for the listbox and one on Worksheets("Sitelookup") for the textboxes.

 

[cjh28]

Would that have to be a seperate sub then to find and populate the textboxes? Any pointers would be great

 

[Andrew Poulsom]

Probably best, because you only need to do it once (not in a loop).

 

[cjh28]

Ah many thanks, got it working now..

I seem to have botched it together pretty successfully and its all working now.

Here's what I came up with:

Option Explicit

Sub locate(Name As String, Data As Range)

    Dim rngFind As Range
    Dim strFirstFind As String
    
     
    With Data
        Set rngFind = .find(Name, LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
                       
            Do
                If rngFind.Row > 1 Then
                    ListBox1.AddItem rngFind.Value
                    ListBox1.List(ListBox1.ListCount - 1, 1) = Data.Parent.Name
                    ListBox1.List(ListBox1.ListCount - 1, 2) = Data.Parent.Name & "!" & rngFind.Address
                    End If
               Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
       End If
    End With
    
End Sub

Sub find(Name As String, Data2 As Range)

    Dim rngFind2 As Range
    Dim strFirstFind As String
    
     
    With Data2
        Set rngFind2 = .find(Name, LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFind2 Is Nothing Then
            strFirstFind = rngFind2.Address

                    'Location
                    With Data2
                    TextBox5.Text = Cells(rngFind2.Row, 2).Text
                    'Speed
                    TextBox10.Text = Cells(rngFind2.Row, 6).Text
                    'Suitability
                    TextBox7.Text = Cells(rngFind2.Row, 4).Text
                    'IP Range5
                    TextBox8.Text = Cells(rngFind2.Row, 8).Text
                    End With
                    End If
               Set rngFind2 = .FindNext(rngFind2)
    End With
End Sub

Private Sub CommandButton1_Click()

    Dim shtSearch As Worksheet
    
       
    ListBox1.Clear
    For Each shtSearch In ThisWorkbook.Worksheets
      
        locate TextBox1.Text, Range("A:M")
        find TextBox1.Text, Worksheets("SiteLookup").Range("A:M")
       
    Next
    If ListBox1.ListCount = 0 Then
        ListBox1.AddItem "No Match Found"
        ListBox1.List(0, 1) = ""
        ListBox1.List(0, 2) = ""
 
     
        
    End If
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim strSheet As String
    Dim strAddress As String
   
    strSheet = ListBox1.List(ListBox1.ListIndex, 1)
    strAddress = ListBox1.List(ListBox1.ListIndex, 2)
   
    If strAddress <> "" Then
        Worksheets(strSheet).Activate
        Range(strAddress).Activate
        
    End If
End Sub

 

[Andrew Poulsom]

A few points:

1. I wouldn't use find as the name of a procedure.

2. You are still searching SiteLookup multiple times because its in the loop.

3. You haven't qualified the Cells property so the active sheet will be used and that may not be what you want.

 

[cjh28]

Ok thanks,

Like I said it was a bit of a botch to get it working, I have now renamed the procedures to more descriptive names of the functions.

In terms of searching site lookup multiple times, how can I get away from this?

It will always be the case that the active sheet will be search so Im not too worried about that but I will have a look at that if I get the chance.

Thanks so much for the help ---- much appreciated

 

[Andrew Poulsom]

In terms of searching site lookup multiple times, how can I get away from this?

Just move this line:

find TextBox1.Text, Worksheets("SiteLookup").Range("A:M")

outside the loop.

 

[cjh28]

Sorry for the delay in gettting back to you, I have now resolved the loop issue. However I have just noticed that If I search for something and its not found in the activesheet i get the error

runtime error '5'

invalid procedure call or argument

Is there any way to avoid this?


Thanks again