Cell data being imported incorrectly

[ipbr21054]

Hi,
On a mileage worksheet in cell C32 i have a value of £23.40
This cell has the formula of =DOLLAR(D31*0.45) so all is ok.

I then run a macro which copies this cell data to another sheet.
Upon that sheet the columns are added up but i see an issue.
In each cell i see this,
Using these as examples,
Cell I30 shows £66.57 address bar shows 66.57 "NO £ SIGN"
Cell I36 shows £259.47 address bar shows 259.47 "NO £ SIGN"

My problem cell I35 is the data from the mileage worksheet.
This cell shows £23.40 address bar shows "£23.40

Because of this my grand total is incorrect as the "£23.40 being shown is not included in the autosum etc.

As a test if i manually type £23.40 in cell I35 problem solved.

 

[MagnarH]

I assume you mean the formula bar when you say address bar.
the simple answer is that the value in the cell is Text-format from excel point of view. Not sure what your code does, but it should only copy the nummeric value, and not include the £-sign into cell itself.

23.40 in the cell is nummeric, and then you can format to SHOW the £.
£23.40 in the cell text and cannot be formated in anyway.

you can try to multiply by 1 to turn it into a number, either by formula or even in the vba code itself.

 

[ipbr21054]

Yes the formula bar.

The code on the mileage sheet should just copy the value in a specific cell and then paste it into a specific cell on my summary sheet.

When home I will advise the code that is in place so you might see the reason why.

 

[Fluff]

The DOLLAR function returns a text value, hence your problem.

 

[ipbr21054]

Thanks Fluff
How can this be written another way the =DOLLAR(D31*0.45)

Not sure why DOLLAR is there ?
Maybe just =SUM(D31*0.45) then format the cell to have it show £
What do you think ?

 

[Fluff]

Just =D31*0.45 & format accordingly

 

[ipbr21054]

That sorted it,many thanks

 

[Fluff]

You're welcome & thanks for the feedback