Excel 'Convert to Number' via VB code 2

[StevenK]

I'm putting together some information in Excel (2007) via a macros which is run under a button click.

The resultant cells contain some numeric information - i.e. '£100.00', '£200.00', etc.
When these are displayed in Excel they show the small green triangle in the top left corner of the cell indicating there is an issue. When you hover over the warning sign next to the cell it displays "The number in this cell is formatted as text or preceded by an apostrophe". When the warning icon is clicked you get the option to 'Convert to Number' - which then removes the small green triangle top left in the cell.
How can this same trick ('Convert to Number' option) be done via VB code in a macros.

I've tried recording a macros but this trick is not recorded.

Any suggestions would be appreciated.

 

[PHV]

yourCell.Value = Val(yourCell.Value)

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

 

[StevenK]

That doesn't look to work. If I run

Cells(141, 3) = Val(Cells(141, 3))

It changes my entry value of '£3.99' to a '0' (zero) value.

 

[SkipVought]

...or just do an Edit > Replace - £ with [NADA] on the sheet.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[StevenK]

Thanks for the suggestion.
But I need to be doing this within VB code within a macros - but the least amount of user intervention as possible.

 

[SkipVought]

Turn on your macro recorder and record the Edit > Replace!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Cells(141, 3) = Val(Replace(Cells(141, 3), "£", ""))

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

 

[StevenK]

I don't wish to remove the '£' formatting - I want to remove the little green triangle as detailed in my original post.
The display of '£3.99' is correct - I just want the little green triangle gone.

 

[PHV]

With Cells(141, 3)
.Value = Val(Replace(.Value, "£", ""))
.NumberFormat = "£#,##0.00"
End With

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

 

[SkipVought]

The £ apparently is the leading CHARACTER. Consequently you have a STRING, rather than a NUMBER.

REMOVE the £, either using Edit > Replace or VBA code, as PHV has suggested...

...and THEN Format the NUMBER as CURRENCY. That will DISPLAY the £, but the underlying VALUE will be NUMERIC.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[StevenK]

Excellent.

Thank you to you both. This is now working as desired for me.