wrong outcome

[kelvin_9]

hello all buddies, whats problem with my code below here, how come it returns column I-M with a wrong sequence? (column A-H is correct & prefect)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DATE[/TD]
[TD]CELL PHONE[/TD]
[TD]FAX[/TD]
[TD]REGION[/TD]
[TD]EMAIL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]expect[/TD]
[TD]15-July-2005[/TD]
[TD]1234[/TD]
[TD]5678[/TD]
[TD]NY[/TD]
[TD]abc@hotmail.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]the outcome with below code[/TD]
[TD]NY[/TD]
[TD][/TD]
[TD]15-July-2005[/TD]
[TD]1234[/TD]
[TD]5678[/TD]
[TD]abc@hotmail.com[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks


Option Explicit
'Private variables
Dim cNum As Integer
Dim X As Integer


Dim nextrow As Range
'error handler
On Error GoTo errHandler:
'set the next row in the database
Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'check for values in the first 4 controls
For X = 1 To 4
If Me.Controls("Reg" & X).Value = "" Then
MsgBox "You must add all data"
Exit Sub
End If
Next
'check for duplicate payroll numbers
If WorksheetFunction.CountIf(Sheet2.Range("D:D"), Me.reg4.Value) > 0 Then
MsgBox "This staff member already exists"
Exit Sub
End If
'number of controls to loop through
cNum = 21
'add the data to the database
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the controls
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next
'sort the database
'Sortit
'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"

 

[kelvin_9]

I've absolutely no idea.
Remove you error handler & see what line of code fails

'declare the variables
Dim cPayroll As String
Dim I As Integer
Dim findvalue
'error block
'On Error GoTo errHandler:
'get the select value from the listbox
For I = 0 To lstlookup.ListCount - 1
If lstlookup.Selected(I) = True Then
cPayroll = lstlookup.List(I, 1)
End If
Next I
'find the payroll number
Set findvalue = Sheet2.Range("D:D").Find(What:=cPayroll, LookIn:=xlValues).Offset(0, -3)
'add the database values to the userform
cNum = 13
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next
'disable adding
Me.cmdadd.Enabled = False
Me.cmdedit.Enabled = True
'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"

 

[Fluff]

In that case it can't find "cPayroll"

 

[kelvin_9]

In that case it can't find "cPayroll"

how can i run in column D and lookup with SAP without error?
i had go through againd and didn't find cpayroll

 

[Fluff]

Try

Set findvalue = Sheet2.Range("D:D").Find(What:=cPayroll, lookIn:=xlValues)
If findvalue Is Nothing Then
   MsgBox cPayroll & " not found"
   Exit Sub
Else
   Set findvalue = findvalue.Offset(, -3)
End If

 

[kelvin_9]

Try

Set findvalue = Sheet2.Range("D:D").Find(What:=cPayroll, lookIn:=xlValues)
If findvalue Is Nothing Then
   MsgBox cPayroll & " not found"
   Exit Sub
Else
   Set findvalue = findvalue.Offset(, -3)
End If

"kelvin, LWH not found"
do you need the workbook?

 

[Fluff]

In that case "kelvin, LWH" does not exist in col D on the sheet with codename Sheet2.
Are you sure that you're looking at the correct sheet?

 

[kelvin_9]

In that case "kelvin, LWH" does not exist in col D on the sheet with codename Sheet2.
Are you sure that you're looking at the correct sheet?

i have adjust from "Offset(, -3)" to "Offset(, -2)" while in column C and sheet is correct
THANK YOU VERY MUCH FLUFF

 

[Fluff]

Glad you got it working & thanks for the feedback