Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    For Each column in Table format cells

    Hello

    The attached Excel 2003 workbook contains 2 macros TblFmtGet and TblFmtSet, which can be run from the attached Tst1 toolbar. TblFmtGet works, but as explained below I'm having problems with TblFmtSet.
    Sheet Original contains a formatted table (tbl_1Main).
    I copied tbl_1Main from sheet Original to sheet Copy.
    I want a macro to apply the formatting in Original to Copy: TblFmtGet outputs cell formatting info in Original.
    I copied the cell formatting info produced by TblFmtGet from Original to Copy, then ran TblFmtSet, which is where I need the help.

    The user would select (in this case) cell C23 of sheet Copy, then run TblFmtSet, with the aim of formatting the table using the formatting info below the table.

    Q.1: I like the idea of using a “for each x in y” type syntax, but the current code with its offset to obtain the required info isn’t working. Is it doable?

    Q.2: For horizontal and vertical alignment TblFmtGet returns the numeric values of the constants; however to apply formatting it seems I have to apply the constant (eg xlRight) not its numeric value (eg -4152). How do I get TblFmtSet to recognise the number provided by TblFmtGet, whether by lookup, conversion, or …?

    Regards, huon03
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,466
    Thanks
    3
    Thanked 130 Times in 123 Posts
    Try this loop
    Code:
    For j = 1 To rTbl.Columns.Count
            With rTbl.Columns(j)
              .ColumnWidth = rCel.Offset(0, j - 1)
              .HorizontalAlignment = rCel.Offset(1, j - 1)
              .VerticalAlignment = rCel.Offset(2, j - 1)
              .WrapText = rCel.Offset(3, j - 1)
              .NumberFormat = rCel.Offset(4, j - 1)
              .RowHeight = rCel.Offset(5, j - 1)
              .Font.Name = rCel.Offset(6, j - 1)
              .Font.Size = rCel.Offset(7, j - 1)
            End With
          Next j
    You could simplify the j-1 to j if you selected the cell B2 before starting the macro.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    huon (2014-04-26)

  5. #3
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello Andrew
    Thank-you for quick response. Your code works well - except for the same problem with H and V alignment. Any thoughts on converting numbers to constants.
    Regards, huon

  6. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,466
    Thanks
    3
    Thanked 130 Times in 123 Posts
    It seems to work if you ensure you are passing in an integer
    .HorizontalAlignment = Int(rCel.Offset(1, j - 1))
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    huon (2014-04-27)

  8. #5
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks again Andrew. I don't understand why - I thought it already was an integer - it's certainly a number.
    I'm a big fan of the NZ author Peter Temple - most of his books are based around Melbourne and several have been adapted for TV. There's another, "Black Tide", on ABC1 tonight (Sun) at 10:12pm.
    huon

Tags for this Thread

Posting Permissions

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