converting a string back to number (value) for calculations

[De Vo]

Hi All

Can I ask some assistance with this one.

Basically I have taken a number and used the formatcurrency() function
to change in my table

156.34 to £156.34

this happends for each cell in my table.

I now need to do a caculation at the end with some of the columns which
included testing for the type of calculation dependant on value

but i am struggling to find a way to remove the formating from the
value or returning the value from the cell to be able to create a
caculation


Dim strCellValue As String
Dim strSubTotal As String
Dim strTempStore As string

Dim intTableRow As Integer


For intTableRow = 2 To ActiveDocument.Tables(1).Rows.Count
strTempStore = .Cell(intTableRow, 3).Range.Text
strTempstore = val(Tempstore)
strSubTotal = Val(strSubTotal) + strTempstore
Next

strSubTotal = FormatCurrency(strSubTotal,2,vbtrue,vbfalse,vbtrue)

ActiveDocument.Tables(2).Cell(1,2).Range.Text = strSubTotal

but I seem to be having problems is there a better way to do what I am
doing ?

please advise with all and any possibilites

Many thanks

De Vo

 

[Peter Hewett]

Hi DeVo

Try using the following code, it expects a table cell to be passed to it
and it returns a numeric value. Any single character non numeric prefix is
removed from the number. Any empty cell returns a zero value:

Public Function CleanCellValue(ByVal celItem As Word.Cell) As Currency
Dim strValue As String
Dim strChar As String

' Clean up cell contents
strValue = celItem.Range.Text
strValue = Left$(strValue, Len(strValue) - 2)

' Strip any non numeric single character prefix
strChar = Left(strValue, 1)
If IsNumeric(strChar) Then
If LenB(strValue) = 0 Then Exit Function
CleanCellValue = strValue
Else
If Len(strValue) < 2 Then Exit Function
CleanCellValue = CCur(Mid$(strValue, 2))
End If
End Function

You use the code something like this:

Dim curValue As Currency
curValue = CleanCellValue(ActiveDocument.Tables(1).Cell(1,1))

HTH + Cheers - Peter

 

[De Vo]

Hi Peter

Once again you help me greatly..

thanks for that..
the main problems I was having which I had was looking me at my face
and the old chr13 + chr7 that was making me pull my hair..

i appreciate the code... it will be used well

thanks again

 

[Peter Hewett]

Hi De Vo

Glad I was able to help!

Also, I noticed that the following line is superfluous:
If LenB(strValue) = 0 Then Exit Function

The current version will through an error if a cell that is not empty
contains a non numeric value (say "123A"). If you want a version that will
just return a zero in these situations, use this version:

Public Function CleanCellValue(ByVal celItem As Word.Cell) As Currency
Dim strValue As String
Dim strChar As String

' Clean up cell contents
strValue = celItem.Range.Text
strValue = Left$(strValue, Len(strValue) - 2)

' Strip any non numeric single character prefix
strChar = Left$(strValue, 1)
If IsNumeric(strChar) Then

' If the cell does not contain a numeric
' value exit with a zero value
If IsNumeric(strValue) = False Then Exit Function
CleanCellValue = strValue
Else

' Strip leading non numeric character
strValue = Mid$(strValue, 2)

' If the cell does not contain a numeric
' value exit with a zero value
If IsNumeric(strValue) = False Then Exit Function
CleanCellValue = CCur(strValue)
End If
End Function

HTH + Cheers - Peter