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--------------------------
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>------------------------------------
Code
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--------------------------
Entries | Output |
$0 | 0 |
$10-$15 | 10 |
10% , $5 | 5 |
0.5 | 0 |
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
Code
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
Last edited by a moderator: