Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Setting cell conetent 1

Status
Not open for further replies.

eon5

Technical User
Dec 31, 2007
47
ZA
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
 
i trim the content from both sides with a macro
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
i trim it with code
Again, with which code ?
 
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)
 
A starting point:
ActiveCell.Value = Val(ActiveCell.Text)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks a mil, works perfecly now
 
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
 



faq68-6659.

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

Part and Inventory Search

Sponsor

Back
Top