How to get Float type from general type in VBA

[Vicasso]

Hi,

I want to extract amount value in my worksheet. So every number after $ is to be shown in output and their is an edge case whenever the number is less then 1 it should be shown in output . I have created a dollarperc function in VBA to get the output. It's working whenever i encounter a $ value but it shows 0 for decimal entries. PFA my macro enabled workbook. Please help, i'm badly stuck here.

Worksheet--------------------------
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Entries[/TD]
[TD="class: xl63, width: 64"]Output[/TD]
[/TR]
[TR]
[TD="class: xl63"]$0[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]$10-$15[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD="class: xl63"]10% , $5[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.5[/TD]
[TD="class: xl63"]0[/TD]
[/TR]
</tbody>[/TABLE]
------------------------------------
Code

Function dollarperc(ByVal str As String) As Double
Dim Matches As Object
Dim Amount  As Double


With CreateObject("VBScript.RegExp") '$number-number
    .Global = False
    .Pattern = "^\$(\d+)-"
    
    If .test(str) Then
        Set Matches = .Execute(str)
        Amount = Matches(0).submatches(0)
        dollarperc = Amount
    End If
End With


With CreateObject("VBScript.RegExp") '$number
    .Global = False
    .Pattern = "^\$(\d+)"
    
    If .test(str) Then
        Set Matches = .Execute(str)
        Amount = Matches(0).submatches(0)
        dollarperc = Amount
    End If
End With


With CreateObject("VBScript.RegExp") '% or $number
    .Global = False
    .Pattern = ", \$(\d+)"
    
    If .test(str) Then
        Set Matches = .Execute(str)
        Amount = Matches(0).submatches(0)
        dollarperc = Amount
    End If
End With
End Function

 

[Peter_SSs]

It seems that you have shown the output from your UDF & said that it is not doing exactly what you want. What would be helpful is to know exactly what output you do want, and what the layout of that output should be. So, can you post the sample data again and show the full desired output?


BTW, when posting code, please use code tags to preserve the code indentation. I have done that for you in your post and my signature block below tells you how.

 

[Kyle123]

Please post what you want the output to look like for each of the examples above, rather than what your code actually outputs

 

[mohadin]

Hi
Just guess

Function dollarperc(ByVal str As String) As Double
Dim Matches As Object
Dim Amount  As Double
With CreateObject("VBScript.RegExp") '$number
    .Global = True
    .Pattern = "((\$)(\d+))|(\d+\..)"
    
    If .test(str) Then
        Set Matches = .Execute(str)
        Amount = Matches(0).submatches(2)
     If Matches(0).submatches(2) = "" Then Amount = Matches(0).submatches(3)
    End If
End With
dollarperc = Amount
End Function