Setting cell conetent 1

[eon5]

Hi,

I have a cell with the value 00023455/2 in it, then i trim the content from both sides with a macro to the following value 23455

My problem is that one of my vlookups still don't see this as a unique number 23455. Thus the vlookup fails. i can even see that the cell content aligns itself to the left as if not recognising it as a value, but the momemt i click on the cell and on the content in the formula bar and enter then the value aligns to the right as a number.

How can i rectify this with coding?

any suggestions will be good

Thanks
eon5

 

[PHV]

i trim the content from both sides with a macro
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eon5]

The cell content starts out as 00033433/3 then i trim it with code to 33433 and it works perfectly but the problem is that excel should then recognise it as a numeric number 33433 but it does not, i tried to fromat the cell to number, general etc but it does not recognise it as the numeric number 33433.

 

[PHV]

i trim it with code
Again, with which code ?

 

[eon5]

I trim from the right with this code:

Sheet13.Range("p" & y).Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],5)"

I trim from the left with this code:

Sheet13.Range("q" & y).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-14],8)

 

[PHV]

A starting point:
ActiveCell.Value = Val(ActiveCell.Text)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eon5]

Thanks a mil, works perfecly now

 

[xlbo]

Do you understand why?

The trim function works on strings so the output is a string (text). What PHV has posted converts that text value to a number

This will happen whenever you use a text operator on a variable in code...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

faq68-6659.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]