Run-time error '1004' for looking up date in worksheet

[streamaxi]

I have an userform (Name=MyForm) with lots of controls. Two of them are
Employee ID (Name=aEmpID) and Employee (Name=aName).

I have tried lot of things in the Private Sub aEmpID_AfterUpdate() but
not successful. What I want is, if I enter the employee ID, it should
search that ID in the worksheet named "DB" in column "A" and take the
corresponding Employee name from column "B" and populate in aName.value
control.

Private Sub aEmpID_AfterUpdate()
Dim rCount As Integer
Dim lRow As Integer
rCount = 1
lRow = Sheets("DB").Range("A65536").End(xlUp).Row

If Len(aEmpID.Value) <> 5 Or Not IsNumeric(aEmpID.Value) Then MsgBox
"Incorrect Employee ID"

For i = 1 To lRow
If Sheets("DB").Range("A & rCount & ").Value = aEmpID.Value Then
aName.Value = Sheets("DB").Range("A & lRow & ").Offset(0,
1).Value
End If
rCount = rCount + 1
Next i
End Sub

I am getting the following error in the line If Sheets("DB").Range("A &
rCount & ").Value = aEmpID.Value Then

Run-time error '1004':
Application-defined or object-defined error

Please help.

 

[streamaxi]

I fogot to mention one thing. The "DB" sheet is hidden and I want it to
be hidden. My active sheet is BLANK and no data in it.

Any urgent help would be appreciated. My deadline is over.

 

[Jim Cone]

Sheets("DB").Range("A & rCount & ").Value
should be...
Sheets("DB").Range("A" & rCount ).Value

Sheets("DB").Range("A & lRow & ").Offset(0, 1).Value
should be...
Sheets("DB").Range("A" & lRow).Offset(0, 1).Value
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



<[email protected]>
wrote in message
I have an userform (Name=MyForm) with lots of controls. Two of them are
Employee ID (Name=aEmpID) and Employee (Name=aName).

I have tried lot of things in the Private Sub aEmpID_AfterUpdate() but
not successful. What I want is, if I enter the employee ID, it should
search that ID in the worksheet named "DB" in column "A" and take the
corresponding Employee name from column "B" and populate in aName.value
control.

Private Sub aEmpID_AfterUpdate()
Dim rCount As Integer
Dim lRow As Integer
rCount = 1
lRow = Sheets("DB").Range("A65536").End(xlUp).Row

If Len(aEmpID.Value) <> 5 Or Not IsNumeric(aEmpID.Value) Then MsgBox
"Incorrect Employee ID"

For i = 1 To lRow
If Sheets("DB").Range("A & rCount & ").Value = aEmpID.Value Then
aName.Value = Sheets("DB").Range("A & lRow & ").Offset(0,
1).Value
End If
rCount = rCount + 1
Next i
End Sub

I am getting the following error in the line If Sheets("DB").Range("A &
rCount & ").Value = aEmpID.Value Then
Run-time error '1004':
Application-defined or object-defined error
Please help.

 

[streamaxi]

Forgot to mention one thing.

DB is not the active sheet. It is hidden and for some reason I want it
to be hidden. My active sheet is BLANK with no data in it. I want to
finish this asap. Any urgent help would be appreciated.

 

[Tom Ogilvy]

For i = 1 To lRow
If Sheets("DB").Range("A" & rCount).Value = aEmpID.Value Then
aName.Value = Sheets("DB").Range("A" & lRow).Offset(0, 1).Value
End If
rCount = rCount + 1
Next i

 

[streamaxi]

Got the hint from Jim's code and it did the trick. Thanks Jim and Tom
both of you for looking into my problem.