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

Topic

New Reply

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…

Reply | Quote

Replies

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

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

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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


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


Reply | Quote

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.

Reply | Quote