CODEWITHSUNDEEP
excelvba
RiCHiE
RiCHiE

Reputation: 288

Add trailing zeroes and keep them 0.00 for subsequent processing

How can I get values like 38.8 and 1 to be 38.80 and 1.00.

I could get cells to display the value correctly but it's real value remains 38.8 when you then try to move it or do anything with it and in the function bar it shows as 38.8.

Column 17 is the price or double I need two decimal places. I then need to move it to another cell to add to the string I'm replacing with RegEx and keep the decimal places.

Sub MacroReplaceText()
' Declare variables
Dim RowNum As Integer
Dim StrNew As String
Dim DblPrice As Double
' Set starting point of row
RowNum = 2

Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
' Set pattern to search for
regex.Pattern = "-.+"
' Set regex to only find first result
regex.Global = False

'Loop through every row that has a value in column 11
Do While Cells(RowNum, 11).Value <> ""
    'Set Price format
    Cells(RowNum, 17).NumberFormat = "#,##0.00"
    DblPrice = Cells(RowNum, 17)
    'Create replacement text string
    StrNew = "- " & Cells(RowNum, 16) & " @ $" & DblPrice
    
   'Replace found pattern
   'Cells(RowNum, 11) = "STC's " & Cells(RowNum, 11)
   'Cells(RowNum, 11) = regex.replace(Cells(RowNum, 11), StrNew)
   'Cells(RowNum, 15) = Cells(RowNum, 11)
   
    RowNum = RowNum + 1
Loop

End Sub

This is the text
Text needed to replace

This is the result after running macro
Result

Upvotes: 0

Views: 518

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

I'm a little confused. But if you want to keep DblPrice as a Double, and also have DblPrice to appear in your string, formatted as you show in your screenshot, then you can use the vba Format function:

StrNew = "- " & Cells(RowNum, 16) & " @ $" & Format(DblPrice,"Currency")

or similar.

Upvotes: 1

Related Questions