Format Vlookup column with Text

[Ian]

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
-------------------------------------------------------------------------

 

[K Dales]

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

 

[David McRitchie]

Hi Ian,
When you change the format to text, the cell does not actually become text
until you reenter the value. In a helper column place a formula
=ISTEXT(A1) so you can watch it. You will have to change both
the A column in you table, and the A column in your data.

The following macro would probably fix, from my join.htm#reenter page,
change the column to text, and leave it selected before running the macro.

Sub ReEnter()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim cell As Range
Dim ix as Long
tCells = Selection.Count
For ix = 1 To tCells
Selection.Item(ix).Formula = Trim(Selection.Item(ix).Formula)
Next ix
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

If you change a cell's format from one number format to another, the change
is effecitve immediately. But if you change from text to number or number to
text the change is not effective until the value is reentered. Same applies
to General in changing either to or from text or number.

Changing to text will affect how the data would be sorted, but that does not
matter to you since you want an exact match, and an exact match does not
care if the data is sorted or not.

 

[Ian]

Thanks for your help.
I have now formatted the Part No column to Text and the Part Description column to General.
At present there are less than 200 parts in the database so I right clicked on the Part No column
and set the format to Text, then double clicked and pressed Enter for each cell to re-enter the
part numbers, now all is working well, (Vlookup works and now retains the leading zero's).

I had already tried to format the Part No column to text, but what I didn't realise is that the
numbers have to be re-entered again for the format to take affect, but now the double clicking
on each cell and pressing Enter did the trick.

Cheers
Ian

 

[Ian]

One thing that is bugging me, at the moment the Part Description Columns for both worksheets
(the Booking In worksheet and the Database worksheet) are set to 'General' and everything is
working ok, if I set the Part Description Column to 'Text' then when I enter a Part Number in
Column A, the actual formula is returned in Column C, rather than the Value of the Vlookup
formula, why is this?
Is there a way to format the Part Description Column as 'Text' so the Vlookup formula works
or should I leave it formatted as 'General'.

Cheers
Ian