The solution to your problem will depend on how the
DATABASE sheet is set up. I assume you added the new part
numbers to the database sheet, so is the part number
column on this sheet also formatted as General, or is it
Text or Number or what?

I think you will need to format both the Column A you are
talking about and the lookup column on the DATABASE sheet
as Text.

Then, the other issue would be sorting the DATABASE
sheet. VLOOKUP requires a sorted lookup range, and the
alpha sort (cells formatted as Text) will be in a
different order than a numeric sort (cells formatted as
Number). If you have not yet considered this, than
perhaps all you need to do is resort your lookup column?

If you have done all that and it still won't work for you,
the only thing I can think of is to put some code in the
Macro to recognize and properly format the problematic
part numbers - but I can't really advise you how to do
this best without knowing more about the workbook.

-----Original Message-----
Hi
I have the following Macro which uses Vlookup that looks
up a Part Number entered in Column A
and returns the correct Part Description in Column C,
along with the date the job was booked in,
in Column F, and the date the job is due back (10 days
time) in Column G.

The Macro has been working well for the last week or so
but now a new problem has risen.
One of the companies has now started sending in repair
work with Part Numbers starting with a zero,
so when we enter the Part Number of say "0123456" and
press the Enter key, it gets shortened to "123456".
For the Vlookup Macro to work, I have formatted Columns A
and C to "General", which is now knocking
of the leading zero's for these new Part Numbers.
If I format Column A to "Text" so it will not knock off
the leading zero's, then the Vlookup macro
doesn't run properly and returns "#N/A" for Part Numbers
that are already in the DATABASE worksheet.

Is there a way I can format Column A so that it won't
delete the leading zero's in a Part Number, and
Vlookup will still work in Column C.

The Macro I am using is as follows:
----------------------------------------------------------
---------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 Then
With Range("F" & Target.Row)
.Value = Date
.NumberFormat = "dd/mm/yy"
End With

With Range("G" & Target.Row)
.Value = Date + 10
.NumberFormat = "dd/mm/yy"
End With

With Range("C" & Target.Row)
.Formula = "=IF($A" & Target.Row
& "="""","""",VLOOKUP($A" & _
Target.Row & "," & "DATABASE!$A$2:$B$1000,2,FALSE))"
Range("C" & Target.Row).Value = Range("C" &
Target.Row).Value
End With
End If
End Sub
----------------------------------------------------------
---------------


.