Macro Set Sheet Range and Row

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
Good day everyone-

The following code searches for a partial match and returns the results in a listbox on a userform. The code unfortunately also returns the headers in row 2. How can I set this part of the doce so that its starts on row 3
Code:
With Sheet2.Range("E:E")
. I tried to set the range to Range("E3:E10003") but, that returned the first item in row 3 at the bottom of the items returned in the listbox... which is not my desired result.

Beet regards,
Charlie

Code:
Sub Lookup()
'declare the variables
    Dim rngFind As Range
    Dim strFirstFind As String
    'error statement
    On Error GoTo errHandler:
    'clear the listbox
    lstLookup.Clear
    'look up parts or all of full mname
    With Sheet2.Range("E:E")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, 1)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, 2)
                    lstLookup.List(lstLookup.ListCount - 1, 3) = rngFind.Offset(0, 3)
                    lstLookup.List(lstLookup.ListCount - 1, 4) = rngFind.Offset(0, 4)
                    lstLookup.List(lstLookup.ListCount - 1, 5) = rngFind.Offset(0, 5)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
    'disable payroll editing
    Me.Reg4.Enabled = False
    Me.cmdEdit.Enabled = False
    'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I took a different approach... I deleted the header names in the cells on row 2 and inserted text boxes with the header names. Now when the macro runs it won't pickup the text boxes.

Thanks for all of you that looked at my question.
Charlie
 
Upvote 0
You quote:
The code unfortunately also returns the headers in row 2

Are you sure you have a header in row 2 or do you mean Column 2
And did you write this script and understand what it's doing or if this a script you copied pasted here and hope you can get it to do what you want?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top