Runtime Error 1004 - Vlookup error when value is a number

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top