Convert numbers stored as text to numbers

Question

this code does not seem to work well always when copying currency data from another sheet:

Dim myprice As String
myprice =   othersheet.Range("H" & c.Row).Value
ws.Range("C" & r).Value = myprice
ws.Range("C" & r).Style = "Currency"

sometimes cells have a warning that "this number is formatted as text"

Answer 1

Try declaring myprice as a Currency.

Answer 2

Have you tried using NumberFormat of the cell

ws.Range("C" & r).NumberFormat = "$#,##0.00"

Answer 3

Excel's "Number Stored as Text" issue can be a bit vexing.

The Help recommendation is to perform a conversion operation by multiplying the value by 1. In practice, however, you're probably better off checking that myprice is numeric and then proceeding accordingly.

For instance:

Dim myprice As String
myprice =   othersheet.Range("H" & c.Row).Value
If IsNumeric(myprice) Then
    myprice = CCur(myprice)
Else
    'Catch non-numeric accordingly
    myprice = "Couldn't convert to number"
End If
ws.Range("C" & r).Value = myprice
ws.Range("C" & r).Style = "Currency"

HTH