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]

So what range is assigned to Data?

 

[cjh28]

From my understanding, and baring in mind I've had a lot of assistance with this as this is my first Userform, the range would be anything A:M in any worksheet within the workbook which is required as the listbox allows me to navigate between worksheets.

 

[Andrew Poulsom]

Please post the code that assigns a range object to Data and calls the locate procedure.

 

[cjh28]

I included all the code on my last post but here's the bit your after :

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) = ""
 
     
        
    End If
End Sub

Thanks

 

[Andrew Poulsom]

So Data.Parent will return a reference to the sheet being searched (shtSearch). Example:

With Data.Parent
'   Location3
    TextBox5.Text = .Cells(rngFind.Row, 2).Text
'   Speed8
    TextBox10.Text = .Cells(rngFind.Row, 6).Text
'   Suitability12
    TextBox7.Text = .Cells(rngFind.Row, 4).Text
'   IP Range5
    TextBox8.Text = .Cells(rngFind.Row, 8).Text
End With

 

[cjh28]

Ah I see, but what this is doing is returning the correct information from the correct row in SiteInfo (sheet 2), is there a way to switch this round so that I recieve the information from the correct row in SiteLookup (sheet 1) which will be the active sheet?

 

[Andrew Poulsom]

Sorry I don't understand. Why are you looping around the worksheets if you want to find the information only on sheet SiteLookup?

 

[cjh28]

The only reason I'm looping through the worksheets is to populate the listbox allowing you to see which sheets have the information in them and allowing you directly to link directly to the raw data.

The reasoning behind the text boxes is to provide a snapshot of the data from the SiteLookup to the user.

Does that clarify things a bit better?

I've got a feeling that i've gone about the solution in the wrong way, but its the only way I knew

 

[Andrew Poulsom]

Sorry, I'm lost. If you only want to use the SiteLookup sheet you don't need to loop around the worksheets. Just do:

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

 

[cjh28]

the problem with that solution, whilst it works for the textboxes and populates the correct information it no longer allows the List box function.

Just to break it down a bit more, there is 2 functions to this form really.

1. the listbox, searches the workbook and provides a list of the worksheets the searched string appears in and allows the user to link direct to the found information

2. the Textboxes, these provide a snapshot of important information from the search. However it needs to provide information from Sitelookup not SiteInfo.

Does that clarify things a bit better?