0

Below VBA Code is working fine if FindVal is a Text value.

1. Working For String Value

Private Sub FDACNo_Change()
Dim myRange As Range
Set myRange = Worksheets("FD Accounts").Range("FDAccountData")
On Error Resume Next
FindVal = FDACNo
Names.Value = Application.WorksheetFunction.VLookup(FindVal, myRange, 2, False)
If err.Number <> 0 Then Names.Value = ""
End Sub

Afterthat I changed this code (Diclare Dim FindVal as Long) to use this code for Numeric Value. Like below.

2. Not Working For Numerical Values

Private Sub FDACNo_Change()
Dim myRange As Range
Dim FindVal As Long
Set myRange = Worksheets("FD Accounts").Range("FDAccountData")
On Error Resume Next
FindVal = FDACNo
Names.Value = Application.WorksheetFunction.VLookup(FindVal, myRange, 2, False)
If err.Number <> 0 Then Names.Value = ""
End Sub

But it gives me an Object Required Error Message.

Note: FDACNo and Names are name of two TextBox fields in a UserFor.

FDACNo is a numerical value, so I used CStr(FindVal) to convert it to a String Value like this:

Names.Value = Application.WorksheetFunction.VLookup(CStr(FindVal), myRange, 2, False)

But no luck

Please Help.

Prabhat Vishwas
  • 65
  • 1
  • 8
  • 2
    You don't use `Set` with data values. Use `FindVal = FDACNo` – Rory Mar 02 '23 at 14:13
  • 1
    As stated above, `FindVal` not being an object, it is a mistake to `Set` it. This code returned the error. Besides that, since you could find strings in the same range, it mai be working as you try or not, **if the range in discussion is formatted As text**. In such a case you should use `Application.WorksheetFunction.VLookup(CStr(FindVal),...`. – FaneDuru Mar 02 '23 at 14:16
  • Hey @FaneDuru, how are you? good to see you. I don't know how but **Cdbl** solved my problem. Anyway, Thanks for giving me an great Idea **CStr**. – Prabhat Vishwas Mar 02 '23 at 16:50

0 Answers0