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

Madter

New Member
Joined
Oct 16, 2017
Messages
24
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:
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
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
Code:
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.
 
Last edited:
Upvote 0
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:
Code:
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"
 
Upvote 0
Please post the entire code of your findAndReplace procedure. Without seeing that, we cannot help you.
 
Upvote 0
Sorry
Code:
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
 
Upvote 0
Word VBA is not my thing, but maybe try changing this line:
Code:
        .Application.Selection.TypeText val
to this:
Code:
        .Application.Selection.TypeText Format(val,"0.00")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top