I am very new to VBA and I have combed the board for 2 days trying to figure this out to no avail. I have a workbook with 2 userforms and 2 data sheets. The EMP userform (saves to EMPdata sheet) has an Emp No TB field and a LAST NAME TB field. I want to be able to search EITHER of these fields on the EMPdata sheet to bring the record to the UserForm.
The code I am using Private Sub cmdLOOKUP_Click(). It works for Test 2 which looks up the last name, but not for the EMPNO. It just erases the field.
Private Sub cmdLOOKUP_Click()
Dim test2 As String
Dim test1 As String
Dim FoundRange As Range
Dim FoundRange1 As Range
test1 = EMPNOE.Value
test2 = txtLASTNAME.Value
Worksheets("EMPData").Activate
Set FoundRange = Sheets("Empdata").Range("D:D").Find(What:=test2, LookIn:=xlFormulas, LookAt:=xlWhole)
If FoundRange Is Nothing Then
Response = MsgBox("No match for " & Me.txtLASTNAME.Value, , vbOK)
If Response = vbOK Then
cmdEMPTY
End If
Else
EMPNOE.Text = FoundRange.Offset(0, -3).Value
txtLASTNAME.Text = FoundRange.Offset(0, 0).Value
FIRSTNAME.Text = FoundRange.Offset(0, -2).Value
MName.Text = FoundRange.Offset(0, -1).Value
SOCIALSECNO.Text = FoundRange.Offset(0, 1).Value
cboEMP.Value = FoundRange.Offset(0, 2).Value
DATEOFHIRE.Text = FoundRange.Offset(0, 3).Value
DOT.Text = FoundRange.Offset(0, 4).Value
cboSEX.Value = FoundRange.Offset(0, 5).Value
POSITION.Text = FoundRange.Offset(0, 6).Value
cboWCCLASS.Value = FoundRange.Offset(0, 7).Value
cboMARITAL.Value = FoundRange.Offset(0, 8).Value
cboPAYROLL.Value = FoundRange.Offset(0, 9).Value
STARTPAY.Text = FoundRange.Offset(0, 10).Value
CURRENTPAY.Text = FoundRange.Offset(0, 11).Value
RAISEDATE.Text = FoundRange.Offset(0, 12).Value
RAISETYPE.Text = FoundRange.Offset(0, 13).Value
DOB.Text = FoundRange.Offset(0, 14).Value
DLSTATE.Text = FoundRange.Offset(0, 15).Value
DLNO.Text = FoundRange.Offset(0, 16).Value
W2STATUS.Text = FoundRange.Offset(0, 17).Value
W2ALLOW.Text = FoundRange.Offset(0, 18).Value
W1ADDWH.Text = FoundRange.Offset(0, 19).Value
HMADD.Text = FoundRange.Offset(0, 20).Value
HOMECITY.Text = FoundRange.Offset(0, 21).Value
STATE.Text = FoundRange.Offset(0, 22).Value
ZIPCODE.Text = FoundRange.Offset(0, 23).Value
HOMEPHONE.Text = FoundRange.Offset(0, 24).Value
MOBILEPHONE.Text = FoundRange.Offset(0, 25).Value
EC1.Text = FoundRange.Offset(0, 26).Value
EC2.Text = FoundRange.Offset(0, 27).Value
EC3.Text = FoundRange.Offset(0, 28).Value
EC4.Text = FoundRange.Offset(0, 29).Value
EC5.Text = FoundRange.Offset(0, 30).Value
EC6.Text = FoundRange.Offset(0, 31).Value
EC7.Text = FoundRange.Offset(0, 32).Value
EC8.Text = FoundRange.Offset(0, 33).Value
EC9.Text = FoundRange.Offset(0, 34).Value
EC10.Text = FoundRange.Offset(0, 35).Value
WORKSHEETNOTES.Text = FoundRange.Offset(0, 36).Value
IRA1.Text = FoundRange.Offset(0, 37).Value
IRA2.Text = FoundRange.Offset(0, 38).Value
cboIRA.Value = FoundRange.Offset(0, 39).Value
IRA4.Text = FoundRange.Offset(0, 40).Value
CAFHIDate.Text = FoundRange.Offset(0, 41).Value
CAFHCovType.Text = FoundRange.Offset(0, 42).Value
CAFHOccCode.Text = FoundRange.Offset(0, 43).Value
CAFHInsRate.Text = FoundRange.Offset(0, 44).Value
CAFDentDate.Text = FoundRange.Offset(0, 45).Value
CAFDentType.Text = FoundRange.Offset(0, 46).Value
CAFDentRate.Text = FoundRange.Offset(0, 47).Value
CAFAccDate.Text = FoundRange.Offset(0, 48).Value
CAFAccRate.Text = FoundRange.Offset(0, 49).Value
CAFHospDate.Text = FoundRange.Offset(0, 50).Value
CAFHospRate.Text = FoundRange.Offset(0, 51).Value
CAFCancerDate.Text = FoundRange.Offset(0, 52).Value
CAFCancerRate.Text = FoundRange.Offset(0, 53).Value
CAFCritInsDate.Text = FoundRange.Offset(0, 54).Value
CAFCritInsRate.Text = FoundRange.Offset(0, 55).Value
CAFDisInsDate.Text = FoundRange.Offset(0, 56).Value
CAFDisCovType.Text = FoundRange.Offset(0, 57).Value
CAFDisInsRate.Text = FoundRange.Offset(0, 58).Value
CAFLifeDate.Text = FoundRange.Offset(0, 59).Value
CAFLifeType.Text = FoundRange.Offset(0, 60).Value
CAFLifeRate.Text = FoundRange.Offset(0, 61).Value
TERMHealthCX.Text = FoundRange.Offset(0, 62).Value
TERMDentalCX.Text = FoundRange.Offset(0, 63).Value
TERMAccCX.Text = FoundRange.Offset(0, 64).Value
TERMHospCX.Text = FoundRange.Offset(0, 65).Value
TERMCancerCX.Text = FoundRange.Offset(0, 66).Value
TERMCritCX.Text = FoundRange.Offset(0, 67).Value
TERMDisabCX.Text = FoundRange.Offset(0, 68).Value
TERMLifeCX.Text = FoundRange.Offset(0, 69).Value
TERMCobraActive.Text = FoundRange.Offset(0, 70).Value
TERMCobraCX.Text = FoundRange.Offset(0, 71).Value
End If
If txtLASTNAME.Text = "" Then
Set FoundRange1 = Sheets("Empdata").Range("A:A").Find(What:=test1, LookIn:=xlFormulas, LookAt:=xlWhole)
If FoundRange1 Is Nothing Then
Response = MsgBox("No match for " & Me.EMPNOE.Value, , vbOK)
If Response = vbOK Then
cmdEMPTY
End If
Else
EMPNOE.Text = FoundRange.Offset(0, -3).Value
txtLASTNAME.Text = FoundRange.Offset(0, 0).Value
FIRSTNAME.Text = FoundRange.Offset(0, -2).Value
MName.Text = FoundRange.Offset(0, -1).Value
SOCIALSECNO.Text = FoundRange.Offset(0, 1).Value
cboEMP.Value = FoundRange.Offset(0, 2).Value
DATEOFHIRE.Text = FoundRange.Offset(0, 3).Value
DOT.Text = FoundRange.Offset(0, 4).Value
cboSEX.Value = FoundRange.Offset(0, 5).Value
POSITION.Text = FoundRange.Offset(0, 6).Value
cboWCCLASS.Value = FoundRange.Offset(0, 7).Value
cboMARITAL.Value = FoundRange.Offset(0, 8).Value
cboPAYROLL.Value = FoundRange.Offset(0, 9).Value
STARTPAY.Text = FoundRange.Offset(0, 10).Value
CURRENTPAY.Text = FoundRange.Offset(0, 11).Value
RAISEDATE.Text = FoundRange.Offset(0, 12).Value
RAISETYPE.Text = FoundRange.Offset(0, 13).Value
DOB.Text = FoundRange.Offset(0, 14).Value
DLSTATE.Text = FoundRange.Offset(0, 15).Value
DLNO.Text = FoundRange.Offset(0, 16).Value
W2STATUS.Text = FoundRange.Offset(0, 17).Value
W2ALLOW.Text = FoundRange.Offset(0, 18).Value
W1ADDWH.Text = FoundRange.Offset(0, 19).Value
HMADD.Text = FoundRange.Offset(0, 20).Value
HOMECITY.Text = FoundRange.Offset(0, 21).Value
STATE.Text = FoundRange.Offset(0, 22).Value
ZIPCODE.Text = FoundRange.Offset(0, 23).Value
HOMEPHONE.Text = FoundRange.Offset(0, 24).Value
MOBILEPHONE.Text = FoundRange.Offset(0, 25).Value
EC1.Text = FoundRange.Offset(0, 26).Value
EC2.Text = FoundRange.Offset(0, 27).Value
EC3.Text = FoundRange.Offset(0, 28).Value
EC4.Text = FoundRange.Offset(0, 29).Value
EC5.Text = FoundRange.Offset(0, 30).Value
EC6.Text = FoundRange.Offset(0, 31).Value
EC7.Text = FoundRange.Offset(0, 32).Value
EC8.Text = FoundRange.Offset(0, 33).Value
EC9.Text = FoundRange.Offset(0, 34).Value
EC10.Text = FoundRange.Offset(0, 35).Value
WORKSHEETNOTES.Text = FoundRange.Offset(0, 36).Value
IRA1.Text = FoundRange.Offset(0, 37).Value
IRA2.Text = FoundRange.Offset(0, 38).Value
cboIRA.Value = FoundRange.Offset(0, 39).Value
IRA4.Text = FoundRange.Offset(0, 40).Value
CAFHIDate.Text = FoundRange.Offset(0, 41).Value
CAFHCovType.Text = FoundRange.Offset(0, 42).Value
CAFHOccCode.Text = FoundRange.Offset(0, 43).Value
CAFHInsRate.Text = FoundRange.Offset(0, 44).Value
CAFDentDate.Text = FoundRange.Offset(0, 45).Value
CAFDentType.Text = FoundRange.Offset(0, 46).Value
CAFDentRate.Text = FoundRange.Offset(0, 47).Value
CAFAccDate.Text = FoundRange.Offset(0, 48).Value
CAFAccRate.Text = FoundRange.Offset(0, 49).Value
CAFHospDate.Text = FoundRange.Offset(0, 50).Value
CAFHospRate.Text = FoundRange.Offset(0, 51).Value
CAFCancerDate.Text = FoundRange.Offset(0, 52).Value
CAFCancerRate.Text = FoundRange.Offset(0, 53).Value
CAFCritInsDate.Text = FoundRange.Offset(0, 54).Value
CAFCritInsRate.Text = FoundRange.Offset(0, 55).Value
CAFDisInsDate.Text = FoundRange.Offset(0, 56).Value
CAFDisCovType.Text = FoundRange.Offset(0, 57).Value
CAFDisInsRate.Text = FoundRange.Offset(0, 58).Value
CAFLifeDate.Text = FoundRange.Offset(0, 59).Value
CAFLifeType.Text = FoundRange.Offset(0, 60).Value
CAFLifeRate.Text = FoundRange.Offset(0, 61).Value
TERMHealthCX.Text = FoundRange.Offset(0, 62).Value
TERMDentalCX.Text = FoundRange.Offset(0, 63).Value
TERMAccCX.Text = FoundRange.Offset(0, 64).Value
TERMHospCX.Text = FoundRange.Offset(0, 65).Value
TERMCancerCX.Text = FoundRange.Offset(0, 66).Value
TERMCritCX.Text = FoundRange.Offset(0, 67).Value
TERMDisabCX.Text = FoundRange.Offset(0, 68).Value
TERMLifeCX.Text = FoundRange.Offset(0, 69).Value
TERMCobraActive.Text = FoundRange.Offset(0, 70).Value
TERMCobraCX.Text = FoundRange.Offset(0, 71).Value
End If
End If
End Sub
Private Sub cmdEMPTY()
EMPNOE.Text = ""
txtLASTNAME.Text = ""
FIRSTNAME.Text = ""
MName.Text = ""
SOCIALSECNO.Text = ""
cboEMP.Value = ""
DATEOFHIRE.Text = ""
DOT.Text = ""
cboSEX.Value = ""
POSITION.Text = ""
cboWCCLASS.Value = ""
cboMARITAL.Value = ""
cboPAYROLL.Value = ""
STARTPAY.Text = ""
CURRENTPAY.Text = ""
RAISEDATE.Text = ""
RAISETYPE.Text = ""
DOB.Text = ""
DLSTATE.Text = ""
DLNO.Text = ""
W2STATUS.Text = ""
W2ALLOW.Text = ""
W1ADDWH.Text = ""
HMADD.Text = ""
HOMECITY.Text = ""
STATE.Text = ""
ZIPCODE.Text = ""
HOMEPHONE.Text = ""
MOBILEPHONE.Text = ""
EC1.Text = ""
EC2.Text = ""
EC3.Text = ""
EC4.Text = ""
EC5.Text = ""
EC6.Text = ""
EC7.Text = ""
EC8.Text = ""
EC9.Text = ""
EC10.Text = ""
WORKSHEETNOTES.Text = ""
IRA1.Text = ""
IRA2.Text = ""
cboIRA.Value = ""
IRA4.Text = ""
CAFHIDate.Text = ""
CAFHCovType.Text = ""
CAFHOccCode.Text = ""
CAFHInsRate.Text = ""
CAFDentDate.Text = ""
CAFDentType.Text = ""
CAFDentRate.Text = ""
CAFAccDate.Text = ""
CAFAccRate.Text = ""
CAFHospDate.Text = ""
CAFHospRate.Text = ""
CAFCancerDate.Text = ""
CAFCancerRate.Text = ""
CAFCritInsDate.Text = ""
CAFCritInsRate.Text = ""
CAFDisInsDate.Text = ""
CAFDisCovType.Text = ""
CAFDisInsRate.Text = ""
CAFLifeDate.Text = ""
CAFLifeType.Text = ""
CAFLifeRate.Text = ""
TERMHealthCX.Text = ""
TERMDentalCX.Text = ""
TERMAccCX.Text = ""
TERMHospCX.Text = ""
TERMCancerCX.Text = ""
TERMCritCX.Text = ""
TERMDisabCX.Text = ""
TERMLifeCX.Text = ""
TERMCobraActive.Text = ""
TERMCobraCX.Text = ""
txtLASTNAME.SetFocus
End Sub
File is attached.
Thank you so much in advance for your assistance.
Bookmarks