Excel VBA - Formatting Ranges


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Excel VBA - Formatting Ranges

Hybrid View

  1. #1
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666

    Excel VBA - Formatting Ranges

    I need to format a specific set of cells (which will be in different locations depending on what I am doing). I need these cells to be center aligned.

    So, I perform the task manually once while recording a macro. The macro codes out as:
    Code:
    Range("C5:E6").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    At the time I need this to run, I have two excel files open. I'm copying data from certain locations on one sheet to the other sheet. The copy part works great. Now I need to do the format part, so I took the macro code and inserted it into my VBA as such.
    Code:
    wkbSummary.Sheets("Sheet1").Range("C" & iRows - 1 & ":E" & iRows).Select
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
    wkbSummary is declared As WorkBook and iRows is declared As Integer.

    When I run it, I get[quote=Run Time Error]Run Time error 1004

    Select method of Range class failed[/code]The wkbSummary.Sheets("Sheet1").Range("C" & iRows - 1 & ":E" & iRows).Select is highlighted.

    I even hardcoded the cell locations in to be C5:E6 and still got the error.

    I'm sure there is yet another mysterious part of Excel I've yet to come to grips with.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  2. #2
    Join Date
    Jan 2009
    Location
    Saint Louis, MO
    Posts
    23
    Try This, add wkbSummary.Activate before you select your range.

    Code:
    wkbSummary.Activate
    wkbSummary.Sheets("Sheet1").Range("C" & iRows - 1 & ":E" & iRows).Select
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = True
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Thanks I'll give this a shot.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Excel VBA - Printing Out Named Ranges
    By Hack in forum VB Classic
    Replies: 4
    Last Post: 09-24-2008, 09:02 AM
  2. Replies: 0
    Last Post: 05-15-2008, 04:24 PM
  3. Replies: 1
    Last Post: 06-04-2007, 04:19 PM
  4. Replies: 1
    Last Post: 01-02-2007, 08:58 AM
  5. Workbook not Closing Properly by Excel VBA
    By blayne in forum VB Classic
    Replies: 1
    Last Post: 11-17-2005, 06:14 PM

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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center