My Lookup does not work because the LookUp field is sometimes of type
1 and sometimes type 2
I tried to fix that in the initial import step by converting the
culprit field to a string as follows :
..
Sub Convert()
Dim LastRow As Integer
LastRow = Range("E65335").End(xlUp).Row
Range("E1:E" & LastRow).NumberFormat = "@"
End Sub
..
The result leads me to believe that the conversion took place because
the data is now is Left-Aligned.
However the data type in that field is still unchanged.
Data in my source field in sometimes Numeric like 51693, sometimes
AlphaNumeric like TN101,
I need to have all that field converted to String or Text.
..
Thank you for your help,
..
J.P.

You do not need to convert everything to text to make VLOOKUP work...

Instead of
=VLOOKUP(A1,...)
Use
=VLOOKUP(TEXT(A1,"#"),...
if your lookup range has text and A1 has a number...
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"u473" wrote:

> My Lookup does not work because the LookUp field is sometimes of type
> 1 and sometimes type 2
> I tried to fix that in the initial import step by converting the
> culprit field to a string as follows :
> ..
> Sub Convert()
> Dim LastRow As Integer
> LastRow = Range("E65335").End(xlUp).Row
> Range("E1:E" & LastRow).NumberFormat = "@"
> End Sub
> ..
> The result leads me to believe that the conversion took place because
> the data is now is Left-Aligned.
> However the data type in that field is still unchanged.
> Data in my source field in sometimes Numeric like 51693, sometimes
> AlphaNumeric like TN101,
> I need to have all that field converted to String or Text.
> ..
> Thank you for your help,
> ..
> J.P.
>

I do appreciate this VLOOKUP tip as I will have the use for it.
However I would like to fix the data type in my initial Import step
with VBA in order to have all data type for that field as type 2.
But I read in another post from Tom Ogilvy : "In general, you can't
force a change in type in VBA the way you can in Excel..."
I could accept that if the conversion was applied within the Cell
within the same Sheet,
but I am reading data in workbook A, converting and writing it in
workbook B. What is the work around solution ?
Thank you for your help,
J.P.