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
. 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
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")
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