VBA overflow/type mismatch

[kelvin_9]

hi, what's the different & problem with my vba and how can i amend it?
i got overflow with this:

Private Sub REG18_AfterUpdate()
If WorksheetFunction.CountIf(Sheet4.Range("a:c"), Me.REG18.Value) = 0 Then
Me.REG18.Value = ""
Exit Sub
End If
With Me
.REG19 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 2, 0)
.REG20 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 3, 0)

End With
End Sub

however, i got type mismatch when i use this

Private Sub REG18_Change()
If WorksheetFunction.CountIf(Sheet4.Range("a:c"), Me.REG18.Value) = 0 Then
Me.REG18.Value = ""
Exit Sub
End If
With Me
.REG19 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 2, 0)
.REG20 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 3, 0)

End With
End Sub

i need someone help, thanks for helping me out and your great help is appreciated!!

 

[DanteAmor]

The code has some details.
If REG18 is blank, then you want to convert a blank with CLng, that sends you a data type mismatch error.

It could work with these changes.

Private Sub REG18_Change()
  If REG18.Value = "" Then Exit Sub
  If WorksheetFunction.CountIf(Sheet4.Range("a:c"), Me.REG18.Value) = 0 Then
    Me.REG18.Value = ""
    Exit Sub
  End If
  If Not IsNumeric(REG18.Value) Then
    MsgBox "Enter numbers"
    Exit Sub
  End If
  REG19 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 2, 0)
  REG20 = Application.WorksheetFunction.VLookup(CLng(Me.REG18), Sheet4.Range("Lookup"), 3, 0)
End Sub

I'm not sure why you want to convert xd content with CLng.

Also try the Find method:

Private Sub REG18_Change()
  Dim f As Range
  
  If REG18.Value = "" Then Exit Sub
  
  Set f = Sheet4.Range("Lookup").Find(REG18, , xlValues, xlWhole)
  If Not f Is Nothing Then
    REG19 = f.Offset(, 1)
    REG20 = f.Offset(, 2)
  End If
End Sub

 

[joeu2004]

I'm not sure why you want to convert xd content with CLng

If Reg18 contains numeric text, IsNumeric(Reg18) returns True, as kelvin_9 might want, but VlookUp(Reg18, Sheet4.Range("Lookup"), 2, 0) would not match if "Lookup" contains numeric values, as kelvin_9's origin code suggests.

However, I would suggest WorksheetFunction.Round(...,0) instead of CLng, unless kelvin_9 purposely wants "banker's rounding".

I would not suggest changing from VLookUp to range.Find without asking kelvin_9 some questions first.

Vlookup compares numeric values (in this case). range.Find compares text, based on the appearance of numeric values due to cell formatting.

So, for example, if Reg18 is 2.4 and the intended matching cell in "Lookup" is a number in the form $2 (e.g. Currency format), VLookup matches, but range.Find does not.

 

[kelvin_9]

thanks danteamor & joeu2004
although i'm not really get into vba code, but i will try the code you provided after shift.
what i want is just like vlookup in a simple worksheet, a product code no.(reg18) entered and populated the product description(reg19) & product price(reg20) same time. thus, i set these 3 textboxes in sheet1, and lookup the value from sheet2, so i use "Application.WorksheetFunction.VLookup", finally i get overflow/type mismatch.
thanks in advance

 

[DanteAmor]

Then use the code with the Find method, in fact, you don't need the search area, if the codes are in column A, you can use the following:

Private Sub REG18_Change()
  Dim f As Range
  
  REG19 = ""
  REG20 = ""
  If REG18.Value = "" Then Exit Sub
  
  Set f = Sheet4.Range("A:A").Find(REG18, , xlValues, xlWhole)
  If Not f Is Nothing Then
    REG19 = f.Offset(, 1)
    REG20 = f.Offset(, 2)
  End If
End Sub

 

[kelvin_9]

this is great DanteAmor, thanks for your helping hand
and i just realize if i want to add&look more data, simply add more reg(textbox) and more offset accordingly.

thanks in anyway! big hand! ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.