Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to loop thru worksheets without selecting them (Excel 2003)

    Hello to all you Excel gurus,

    I am using the following code in a larger macro:

    For Each RngCell In Rng1
    If RngCell.Value = "XYZ Corp" Then
    x = RngCell.Row
    ** With ActiveWorkbook.Sheets(v).Range(Cells(x, 1), Cells(x, c))**
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    End With
    End If
    Next RngCell

    Note the line with the asterisks (fourth line down). This is the problem line. This line will not execute unless the specific sheet is selected. The macro comes to a stop if I try to run it. It gives a Run-time 1004 error. However if I then select the particular sheet (whatever v is), the line will execute properly, but then the same thing happens on the next worksheet.

    I haven't been able to find anything in literature on this, so I don't know how to resolve it. All I would like to do is be able to execute the macro without having to select each worksheet.

    Thanks in advance for any and all assistance...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    This is because Cells(.., ...) refers to the active sheet unless you explicitly specify another sheet. You can use

    With ActiveWorkbook.Sheets(v).Range(ActiveWorkbook.Shee ts(v).Cells(x, 1), ActiveWorkbook.Sheets(v).Cells(x, c))

    Depending on how Rng1 is defined, there may be other, more compact ways.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    The code below works for me. Note that I had to make some assumptions about the code that comes before what you posted.

    <code>
    Public Sub Test()
    Dim oSht As Worksheet, RngCell As Range, Rng1 As Range
    Dim x As Long, c As Long
    c = 10
    For Each oSht In Worksheets
    Set Rng1 = oSht.Range("A1:A20")
    For Each RngCell In Rng1
    If RngCell.Value = "XYZ Corp" Then
    x = RngCell.Row
    With oSht.Range(oSht.Cells(x, 1), oSht.Cells(x, c))
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    End With
    End If
    Next RngCell
    Next oSht
    End Sub
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    Hi Hans,

    Thank you for your reply.

    Actually, I did that but that didn't work either. It kept stopping at that line unless I selected the specific worksheet. I can send the entire macro if you're interested...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    Perhaps you could post a stripped down copy of the workbook, with sensitive data removed or replaced.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    Hi Legare,

    Thank you for your reply. Your code worked like a charm. I was able to integrate it into the larger macro without problem.

    Besides solving the problem it was instructive to me on a couple of levels, as your code often is.

    Thank you again.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to loop thru worksheets without selecting them (Excel 2003)

    Hi Hans,

    Well, it turns out that there is no need, as the code Legare Coleman offered did the trick.

    I'm not sure what I was doing wrong in my original efforts. I did go through and refer to the sheet before every Cell statement but I couldn't get rid of the problem. Perhaps I needed to restart Excel.

    But let me thank you once again for your responses and your efforts. This site really does terrific things.

    Regards,

Posting Permissions

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