Run-time error '6': Overflow

[Bryan123]

Hi All, please help me find a solution on this error. When I enter emp ID on the combobox and if I enter more than the usual number of digits, I get the error. Error is found on this line:

theValue = Application.VLookup(CLng(ComboBox1), rng, 11, False)

Please assist. Here's the code:


Private Sub UserForm_Initialize()
employee = Worksheets("LRS sampling").Range("A2:L200").Value
Dim ListItems As Variant, i As Integer
With Me.ComboBox1
.Clear
Application.ScreenUpdating = False
ListItems = employee
For i = 1 To UBound(ListItems, 1)
.AddItem ListItems(i, 1)
Next i
ComboBox1.ListIndex = 0
End With

End Sub
Private Sub Combobox1_Change()
Dim rng As Range

Set rng = Worksheets("LRS sampling").Range("A2:L200")
If Me.ComboBox1 = "" Then Exit Sub
Dim theValue, thevalue1, division1, process1, cso1, policynum1
theValue = Application.VLookup(CLng(ComboBox1), rng, 11, False)
thevalue1 = Application.VLookup(CLng(ComboBox1), rng, 3, False)
division1 = Application.VLookup(CLng(ComboBox1), rng, 4, False)
process1 = Application.VLookup(CLng(ComboBox1), rng, 9, False)
cso1 = Application.VLookup(CLng(ComboBox1), rng, 2, False)
policynum1 = Application.VLookup(CLng(ComboBox1), rng, 5, False)
If IsError(theValue) Then
insured.Value = "not found"
workstream.Value = "not found"
division.Value = "not found"
process.Value = "not found"
CSO.Value = "not found"
policynum.Value = "not found"
Else
insured.Value = theValue
workstream.Value = thevalue1
division.Value = division1
process.Value = process1
CSO.Value = cso1
policynum.Value = policynum1

End If
End Sub

 

[steve the fish]

What is the value of Combobox1?

 

[Bryan123]

Hi Steve,
7 digit employee number

 

[steve the fish]

You start getting the overflow error when using Long on numbers greater than 2147483647. Try CDbl.

 

[Bryan123]

Hi Steve,

Works like a miracle. Thank you!!