Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Putnam Valley, New York, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Background: I'm cleaning up tables from SEC Edgarized documents and they often they have currency symbols in a separate cell. The irrational boss would prefer to have these symbols in the same cell, but with a tab separating them from the number (this is to approximate the look of the "accounting format").

    I'm not looking to merge the "currency cell" with the "number cell." I'm looking to strip the currency cells MANUALLY, then develop a macro that adds in the currency and a tab (shift-tab) before the number, in the cells I choose to highlight . Also trying to avoid putting this in Excel.

    Below is the start of the macro, but I'm missing some crucial bits. All help appreciated, including any commenting (my VBA skill is akin to a tourist writing technical manual in foreign language, I get the gist of certain nouns, but coming up with the proper verb tense is pure luck: PLEASE TO DEPRESS THE HAPPY BUTTON FOR ENTRANCE

    Code:
    Sub DollarTabInsert ()
    ' Puts in a dollar sign and a tab before a number in a cell
    'This should allow me to vary the number of cells I highlight
    'keyboard shortcut will be Alt-4
    
    Dim oCel As Cell, 
    Dim RngCel As Range
        With Selection         
            If .Information(wdWithInTable) = True Then
                For Each oCel In .Cells
                    Set RngCel = oCel.Range
                    'RngCel.MoveStart Unit:=    '<---- NOT SURE WHAT TO PUT HERE to get beginning of cell (# vary dramatically)
                    'Here I want to tell Word to
                    'insert a dollar sign and a tab before the text in the cell WITHOUT wiping out current text
                Next
            SetRngCel = Nothing
            End If
        End With
    End Sub

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Eowyn,

    Try:
    Code:
    Sub DollarTabInsert()
    ' Puts in a dollar sign and a tab before a number in a cell
    'This should allow me to vary the number of cells I highlight
    'keyboard shortcut will be Alt-4
    
    Dim oCel As Cell
    With Selection
      If .Information(wdWithInTable) = True Then
        For Each oCel In .Cells
          If IsNumeric(Left(oCel.Range.Text, Len(oCel.Range.Text) - 1)) Then _
          oCel.Range.InsertBefore "$" & vbTab
        Next
      End If
    End With
    End Sub
    Note that I've also added some error-checking to prevent the $-tab being added to cells containing text. This gives more flexibility with the range(s) you might need to work with, allowing you to include text cells.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Putnam Valley, New York, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much, this did nicely. I actually had to take out the error checking because I use en-dashes instead of zeros, and apparently that doesn't count as numeric. go figure

    I'm impressing the hell out of my workmates, and that can't be a bad thing in this economy.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Eowyn View Post
    Thanks so much, this did nicely. I actually had to take out the error checking because I use en-dashes instead of zeros, and apparently that doesn't count as numeric. go figure
    Hi Eowyn

    You can keep the error-checking with:
    If IsNumeric(Left(Replace(oCel.Range.Text, Chr(150), "-"), Len(oCel.Range.Text) - 1)) Then _
    With this, the code will work for cells that have either en dashes or standard hyphens.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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