CODEWITHSUNDEEP
vbaexcel
user2839067
user2839067

Reputation: 11

Excel VBA Macro - String to Decimal

I'm trying to convert a cell value containing a coordinate to an integer. The string value is "X-324.45", but would like to be able to use this number for an if then loop later.

Msgbox1 shows the string as X-324.45 Msgbox2 shows the string as -324.45 Msgbox3 shows a blank MsgBox

What am I missing here? I would like -324.45 to be a variable I can use for math operations.

Thanks,

 Dim YVAL1 As String
 Dim YVAL2 As String
 Dim YVAL3 As Double
 YVAL1 = ActiveCell.Offset(0, -4).Value
 YVAL2 = Mid(YVAL1, 1, 10)
 YVAL3 = CDbl(YVAL2)
 MsgBox ("Value1: " + YVAL1)
 MsgBox ("Value2: " + YVAL2)
 MsgBox ("Value3: " + YVAL3)

Upvotes: 0

Views: 9515

Answers (2)

addohm
addohm

Reputation: 2455

YVAL2 = CDec(Mid(YVAL1, 2, 10))

enter image description here

Sub Button1_Click()
    Dim yval1, yval2
    yval1 = Range("A1").Value
    yval2 = CDec(Mid(yval1, 2, 10))
    MsgBox ("Value: " & yval2)
End Sub

Upvotes: 3

Alex K.
Alex K.

Reputation: 175796

Mid(YVAL1, 1, 10) does not remove anything - to read from the 2nd character Mid(YVAL1, 2, 10).

MsgBox ("Value3: " + YVAL3) fails because it attempts the arithmetic addition of a non-convertible string to a double - always use & to concatenate:

MsgBox ("Value3: " & YVAL3)

Upvotes: 3

Related Questions