Using Vlookup between text file & spreadsheet

[tstrobel]

Hello all, I will try to explain my problem. I am using Vlookup to insert alpha-numeric data from a text file into my spreadsheet. Here is part of my code:

Selection.NumberFormat = "000000000"
ActiveCell.FormulaR1C1 = Cusip(i)
ActiveCell.FormulaR1C1 = "=TEXT(" & ActiveCell & ",""000000000"")"

The Cusip info is pulled from SQL. This code is before the Vlookup code. The code above works fine as long as the data is actually all numbers, "023375868" and the Vlookup works correctly. But when I get to this type of data, "00141M515" I get this error message: Run-time error '1004' Application defined or object defined error and won't let me go on.

:help

[Kenneth Hobs]

Welcome to the forum!

As your code does not show the lookup table nor the vlookup, it is hard to know where your problem lies. If you return a value in the code from use of WorksheetFunction.vLookup then you need to handle the case result of #N/A. Use F8 to step through your code to see what line of code gives the error.

[AdrianK]

Vlookups can be troublesome, you're currently defining the cell as a customer number, have you considered making it a text field instead which would cope with both?

As Kenneth said more information on your code would be useful to help.

Adrian