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

 

[Joe4]

The issue is that trailing zeroes have no meaning in decimals of numeric values. You need to convert your entry to text.
Do you always want two decimals?

Is the issue with the value you are passing in or the value you are getting out of your findAndReplace procedure?
If the value you are passing in, use the FORMAT function to ensure that, i.e.

Call findAndReplace([COLOR=#ff0000]Format[/COLOR](Replace(Round(vbaSheet.Cells(i, "U").Value, 2), ",", "."),[COLOR=#ff0000]"0.00"[/COLOR]), "Freight:", 3)

If the value you are getting out, you will need to amend your findAndReplace code to return a String instead of a Number, and use the FORMAT function within that.

 

[Madter]

Yes, i always need two decimals.
But the findAndReplace function does return a value as a string.
This is the findAndReplace code that i have:

Private Sub findAndReplace(val As String, fVal As String, extWord As Long)

Etc.
The problem is with the value that comes out of this.

And i tried your solution, where one number comes out with "," instead of "." - like "944,00" instead of "944.00". And another value comes out like "5324101,01" instead of "53241.01"

 

[Joe4]

Please post the entire code of your findAndReplace procedure. Without seeing that, we cannot help you.

 

[Madter]

Sorry

Private Sub findAndReplace(val As String, fVal As String, extWord As Long)
    With wdDoc
        ' Find value to replace
        .Application.Selection.Find.Text = fVal
        .Application.Selection.Find.Execute
        .Application.Selection.MoveRight unit:=wdWord, Count:=1
        .Application.Selection.MoveRight unit:=wdWord, Count:=extWord, Extend:=wdExtend
        .Application.Selection.TypeText val
        .Application.Selection.MoveDown unit:=wdLine, Count:=1
    End With
End Sub

 

[Joe4]

Word VBA is not my thing, but maybe try changing this line:

        .Application.Selection.TypeText val

to this:

        .Application.Selection.TypeText Format(val,"0.00")

 

[Madter]

Does the same thing as the modification you gave earlier.

 

[Joe4]

So, is the issue that the data being pasted back to Word is losing the trailing zero?
If so, I would recommend handling it in the Word merge field, as shown here: https://www.extendoffice.com/docume...il-merge-date-currency-and-number-format.html

 

[Madter]

The thing is that the macro is in excel, as a master excel file and it handles all the data transfer. The word files, on the other hands, are received every day and are therefore different for every day. It isn't the most difficult task in the world, to manually add the decimals in the word file, after the data transfer. But i just wanted to make the VBA complete. Otherwise it works perfectly, except for this.

 

[Joe4]

We have a person here who is an expert at controlling Word from Excel. I will reach out to him and see if he can help.