Runtime Error 1004 - Vlookup error when value is a number

[Denny57]

Software: MS 365 32-Bit (only because Active X and other tools are not available in 64-Bit version which has caused errors in formulas and other VBA coding)

I am hoping that someone can help me with a problem that has occured since I changed my device and now have to use MS 365.
I have a User Form which uses Vlookup to populate fields and which works perfectly well when the lookup value is alpha but now returns a Runtime 1004 error when the lookup value is numeric. This was not, and is not an issue if I use an earlier version of Excel.

The user form has a number of fields (textboxes) that are populated with lookup information and I get the same result with each. I also have coded the function to show a message box if the lookup value cannot be found and this works fine with Alpha values. But with the numeric values the process generates the runtime error 1004, hence the check is not performed.

Sample Code:

Private Sub txtDepotCode_AfterUpdate()

If WorksheetFunction.CountIf(Sheet3.Range("E3:G400"), Me.txtDepotCode.Value) = 0 Then
MsgBox "This is an invalid code", 0, "Validation Check"
Me.txtDepotCode.SetFocus
Me.txtDepotCode.Value = ""
Exit Sub
End If
With Me
.txtDepotLocation = Application.WorksheetFunction.VLookup(Me.txtDepotCode, Sheet3.Range("E3:G400"), 2, False)
.txtDepotOperator = Application.WorksheetFunction.VLookup(Me.txtDepotCode, Sheet3.Range("E3:G400"), 3, False)
End With

End Sub

Error Message

Run-time error "1004"

Unable to get the Vlookup property of the WorksheetFunction class

I am hoping that this is not caused by using MS365 32-bit

Any help would be most welcome

 

[Denny57]

Software: MS 365 32-Bit (only because Active X and other tools are not available in 64-Bit version which has caused errors in formulas and other VBA coding)

I am hoping that someone can help me with a problem that has occured since I changed my device and now have to use MS 365.
I have a User Form which uses Vlookup to populate fields and which works perfectly well when the lookup value is alpha but now returns a Runtime 1004 error when the lookup value is numeric. This was not, and is not an issue if I use an earlier version of Excel.

The user form has a number of fields (textboxes) that are populated with lookup information and I get the same result with each. I also have coded the function to show a message box if the lookup value cannot be found and this works fine with Alpha values. But with the numeric values the process generates the runtime error 1004, hence the check is not performed.

Sample Code:

Private Sub txtDepotCode_AfterUpdate()

If WorksheetFunction.CountIf(Sheet3.Range("E3:G400"), Me.txtDepotCode.Value) = 0 Then
MsgBox "This is an invalid code", 0, "Validation Check"
Me.txtDepotCode.SetFocus
Me.txtDepotCode.Value = ""
Exit Sub
End If
With Me
.txtDepotLocation = Application.WorksheetFunction.VLookup(Me.txtDepotCode, Sheet3.Range("E3:G400"), 2, False)
.txtDepotOperator = Application.WorksheetFunction.VLookup(Me.txtDepotCode, Sheet3.Range("E3:G400"), 3, False)
End With

End Sub

Error Message

Run-time error "1004"

Unable to get the Vlookup property of the WorksheetFunction class

I am hoping that this is not caused by using MS365 32-bit

Any help would be most welcome

I thought I had resolved this by changing the format of the lookup information in the lookup table to "text" format, however, the error message now returns after I have added a record and then try to input new information into the form.

The add record command code ends with
Call UserForm_Initialize
cboType.SetFocus
which works fine and is the same code used to open the form.

The form uses frames to separate different types of information and after information has been successfully input in fields in frames 1 & 2, as soon as the tab moves to frame three, the runtime error returns. What is confusing me is that frame three contains 5 fields that use Vlookup but when I check the debug code, the error is happening for the last of these fields.

Please can someone provide me with a reason why this only happens after the initial record has been added and why it affects just the last of 5 fields that use VLookup to populate other fields in the form. There does not seem to be any logic why this should only happen after a records has been successfully entered without error messages and the resetting of the form uses the same code as used to open the user form.