Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Seymour, Connecticut, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA MacronHelp Please (Excel 97)

    I wrote the following code in an excel macro to format a column in a small excel database:
    Set rngcol=Range("F3",Range("f65536".End(xlUp))
    For each i in rangeCol
    If i.Value<>"" Then
    i.NumberFormat="General"
    Withi i.Font
    etc
    etc.
    My problem is I want the column, which will contain dollar amounts to be formatted in accounting style. I know the code is incorrect but I don't knoew enough VBA to correct it. Can anyone offer any solutions?

    Thanks in advance

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA MacronHelp Please (Excel 97)

    Hi

    I'm not sure which accounting style you have in mind. If it is the same as the default Currency format, you could do the following:

    <pre>Sub Accounting()
    Columns("F:F").Select
    Selection.Style = "Currency"
    End Sub</pre>

    Alternatively, you could redefine the Currency format as part of the macro by inserting the following code at the beginning of the Sub.
    <pre> ActiveWorkbook.Styles("Currency").NumberFormat = _
    "_-* $#,##0.00_-;* $(#,##0.00);_-* ""-""??_-;_-@_-"</pre>

    adjusting the layout before the first two semi-colons to suit. You could also define a Custom Style.

    HTH
    Gre

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA MacronHelp Please (Excel 97)

    Here's a very quick function that I wrote. Using something like this makes it easier to read your code: who can remember the acctng format perfectly, all the time? Just do a
    Range("whatever range").Select
    fx_format_makeAcctng
    to use it, once you've created it in your module.

    The code is
    Private Function fx_format_makeAcctng()
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End Function

    Hope that helps! : )

Posting Permissions

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