Error 2042 - Returning row information

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
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:

Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
That's work a treat thanks, the issue I'm now getting is that when it returns the information found it return values from a row 12 about the actual results found.

However when I put a break point in (at any point within the textboxs or in the if statement after) and debug it, the values in the TextBox are correct

Any ideas?

Thanks again
 

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
Ok I've had a bit of progress and I've now got to

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

whilst this retrieves the information from the activesheet, the code is actually using the row number from sheet 2 and looking that up on the active sheet and providing me with that information.

For example, it will find 2 instance of a string, sheet1 Row 30 and sheet2 Row 50. The form will now return infromation from sheet1 Row 50. Again in debug mode it seems ok

Thanks again
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

What's the name of ActiveWorkbook.Sheets(1)?
 

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
Within the ActiveWorkbook there is:

Sheet(1) called SiteLookup and Sheet(2) called SiteInfo
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

How do those names fit with this statement "For example, it will find 2 instance of a string, sheet1 Row 30 and sheet2 Row 50. The form will now return infromation from sheet1 Row 50."?
 

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
Sorry if I didnt really clarify the issue that well

Currently whats happening is that when I run the userform on the activeSheet (SiteLookup) the macro will find 2 instances of a string for example, say one is on row 50 of SiteLookup and the other is row 30 of SiteInfo.

The Textboxes will currently return the information contained in SiteLookup but from row 30 rather than row 50. So the userform is taking information from the correct sheet but not the row that relates to that sheet.

So
"For example, it will find 2 instance of a string, in SiteLookup Row 30 and SiteInfo Row 50. The form will now return infromation from SiteLookup Row 50"?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Isn't find being applied to only one worksheet?

Set rngFind = ActiveWorkbook.Sheets(1).Cells.Find(Name, LookIn:=xlValues, LookAt:=xlPart)
 

cjh28

New Member
Joined
Nov 25, 2009
Messages
15
Well that was the aim, but I've had to change the code to

Set rngFind = .Find(Name, LookIn:=xlValues, LookAt:=xlPart)

As its causing an error. I'll repost the entire code just to make sure I havent missed anything.

Code:
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
                                
                    '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 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) = ""
 
     
        
    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

thanks again
 

Forum statistics

Threads
1,143,658
Messages
5,720,128
Members
422,267
Latest member
olund

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top