Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting from Macro (Excel 2003)

    Not sure if this is an Access question or a Excel question...

    I am trying to format a spreadsheet from using VBA code. From a specific range I have the syntax down
    Range("QM1:AP16").Select
    with Selection
    .... and so forth.

    What I need is to be able to make the starting point the AM1 cell, but be able to
    vary the ending point based on the number of rows output to the spreadsheet.
    I have a loop that is outputting the rows, so I have a counter that will tell me how many rows
    were output. The column will always be the AP column.

    Is there a way to put in a relative reference for the range?


    Thanks
    Richard

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting from Macro (Excel 2003)

    You refer to QM, AM and AP as the starting column, so not sure which you want, but generally you can use:
    <pre>With Range("AP1:AP" & RowNum)
    ' do whatever
    End With
    </pre>


    No need to select anything. If you are running htis from Access, you would need to prefix the Range with a worksheet object.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting from Macro (Excel 2003)

    OK, I am confused...

    Here is my code
    With wks
    .Range("AM1:AP" & y).Select
    With Selection.Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic

    End With

    End With

    When I ran the code, it worked once, but failed the second time through.
    When I flip over to look at the spreadsheet, the correct area is selected.

    The error I receive is
    "Object variable or With block variable not set"


    I am having a similar problem with this set of statements

    wks.Range("e6").Select
    ActiveWindow.FreezePanes = True


    Thanks for your help.
    Richard

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting from Macro (Excel 2003)

    If you are automating one application from another, you must fully qualify all references to objects in the automated application. So you should not use <code>Selection</code> but use something like <code>xlApp.Selection</code> (though you should avoid selecting whenever possible). Try this version:

    <pre>With wks.Range("AM1:AP" & y).Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    ....
    wks.Range("e6").Select
    wks.Parent.Parent.ActiveWindow.FreezePanes = True

    </pre>


    As it is, you probably have an invisible instance of Excel open so you will need to close it down before rerunning this code.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting from Macro (Excel 2003)

    That seems to have doone the trick.
    I did think about the closing and had just added the code to
    close and then set the object to nothing.


    Thanks again for your help.
    Richard

Posting Permissions

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