Sting to VALUE conversion

[John]

Hi: I have a VBA function that returns a string which represents a numeric
value. I need to make that string into a value for calculations. I have
tried the "Value" function without any luck.

The formula follows.

Function AFTLAST(cell As Range, findchar As String) As String

Dim i As Long
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = findchar Then
AFTLAST = Mid(cell, i + 1, 99)
Exit Function
End If
Next i
AFTLAST = cell

End Function

This function evaluates a long string which has several numeric characters.
I must be doing something wrong.

 

[John]

I found the problem !!!

The resulting value contains unwanted characters. So I applied
"VALUE(CLEAN( .....) )" to the spreadsheet cell.


All is good

"John" wrote in message
Hi: I have a VBA function that returns a string which represents a numeric
value. I need to make that string into a value for calculations. I have
tried the "Value" function without any luck.

The formula follows.

Function AFTLAST(cell As Range, findchar As String) As String

Dim i As Long
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = findchar Then
AFTLAST = Mid(cell, i + 1, 99)
Exit Function
End If
Next i
AFTLAST = cell

End Function

This function evaluates a long string which has several numeric characters.
I must be doing something wrong.

 

[GS]

Revise as follows...

Hi: I have a VBA function that returns a string which represents a
numeric value. I need to make that string into a value for
calculations. I have tried the "Value" function without any luck.

The formula follows.

Function AFTLAST(cell As Range, findchar As String) As String

Dim i As Long
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = findchar Then
AFTLAST = Mid(cell, i + 1, 99)
Exit Function
End If
Next i
End Function

...then use a formula like...

=VALUE(AFTLAST(?,?))

...where you'd substitute the ? character with actual values! Note that
the revision will return an empty string that should evaluate to zero.

This function evaluates a long string which has several numeric
characters. I must be doing something wrong.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

In that case ignore my other reply and try this...

Function AFTLAST(cell As Range, findchar As String) As String

Dim i&, sTextIn$
sTextIn = FilterString(cell.Value, , False, True)
For i = Len(sTextIn) To 1 Step -1
If Mid(sTextIn, i, 1) = findchar Then
AFTLAST = Mid(sTextIn, i + 1, 99)
Exit Function
End If
Next i
AFTLAST = sTextIn
End Function

Function FilterString$(ByVal TextIn As String, _
Optional IncludeChars As String, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Keeps any characters.
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
'
' Returns: String containing only the wanted characters.

Const sSource As String = "FilterString()"

'The basic characters to always keep
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i As Long, CharsToKeep As String

CharsToKeep = IncludeChars
If IncludeLetters Then _
CharsToKeep = CharsToKeep & sLetters & UCase(sLetters)
If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers

For i = 1 To Len(TextIn)
If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
Next
End Function 'FilterString()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion