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

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

  1. #11
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you!

  2. #12
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    1,763
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Maybe trial...
    Code:
    Call findAndReplace(Replace(CSTR(Round(vbaSheet.Cells(i, "U").Value, 2)), ",", "."), "Freight:", 3)
    HTH. Dave

  3. #13
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #14
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,395
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    Call findAndReplace(Replace(Format(vbaSheet.Cells(i, "U").Value, "0,00"), ",", "."), "Freight:", 3)
    Last edited by FormR; Nov 14th, 2017 at 11:11 AM.
    [code]your code[/code]

  5. #15
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,536
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Personally, I'd skip the to-ing & fro-ing with Excel and use something like:
    Call FindAndReplace("Freight:")
    with:
    Code:
    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.
    Last edited by Macropod; Nov 14th, 2017 at 05:08 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com