Keeping trailing zero's when transferring from excel to word VBA

[Madter]

Hi.
I have this VBA code, where i pull data from word and then i transfer other data back to word.

I have this code:

Call findAndReplace(Replace(Round(vbaSheet.Cells(i, "U").Value, 2), ",", "."), "Freight:", 3)

And this code for example takes this from excel "155,40" and returns it to word as this "155.4".

This problem only occurs when there's a zero at the end of the decimals. But i want the number to be transferred as "155.40".
What can i add to this?

Thank you so much in advance

 

[Madter]

Thank you!

 

[NdNoviceHlp]

Maybe trial...

Call findAndReplace(Replace(CSTR(Round(vbaSheet.Cells(i, "U").Value, 2)), ",", "."), "Freight:", 3)

HTH. Dave

 

[Madter]

Thanks, but it does not make any difference from the original code..

 

[FormR]

Hi, this is just a guess, but is your decimal separator a comma? If so maybe you could try:

Call findAndReplace(Replace(Format(vbaSheet.Cells(i, "U").Value, "0,00"), ",", "."), "Freight:", 3)

 

[Macropod]

Personally, I'd skip the to-ing & fro-ing with Excel and use something like:
Call FindAndReplace("Freight:")
with:

Private Sub FindAndReplace(strFnd As String)
With wdDoc.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = strFnd & "[$€£ 0-9,.]{1,}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    Do While .Characters.Last Like "[,. ]"
      .End = .End - 1
    Loop
    .Start = .Start + Len(strFnd)
    .MoveStartWhile " ", 1 'wdForward
    If .Characters.First Like "[$€£]" Then .Start = .Start + 1
    .MoveStartWhile " ", 1 'wdForward
    If (Right(.Text, 4) Like "#.##") = False Then
      .Text = Format(Replace(Replace(Replace(.Text, ".", ""), " ", ""), ",", "."), "#,##0.00")
    End If
    .Collapse 0 'wdCollapseEnd
    .Find.Execute
  Loop
End With
End Sub

With this approach, any value following 'Freight:' in the Word document will be converted to the #,##0.00 format, including amounts preceded by currency symbols.