Nested For Each Loops -for each ws... for each cell... failing to loop worksheets

arg123

New Member
Joined
Jun 8, 2016
Messages
21
Hello all!

I've looked through pages and pages of searches here and across the web, and I'm back with no luck. Hoping someone will see something that I should be seeing.

I am trying to perform nested loops and the outer loop (worksheets) isn't looping from ws to ws.

When the macro is run, the nested part (cell in range) performs great within the active worksheet. But I want to start at the first worksheet and apply the "For Each cell..." within each worksheet across the whole workbook.

Any thought, I'm all ears! And thanks in advance!

Looks like:

VBA Code:
Sub AnalysisStep3()

Dim ws As Worksheet
Dim rnumrange As Range
Set rnumrange = Range("I2:I27,I32:I65")

For Each ws In Worksheets
        For Each cell In rnumrange
            If cell.Value >= 5 And cell.Value <= 32 Then
                cell.Offset(0, 8).Value = 1
            ElseIf cell.Value >= "0" And cell.Value < 5 Then
                cell.Offset(0, 8).Value = 2
            ElseIf cell.Value <= 40 And cell.Value > 32 Then
                cell.Offset(0, 8).Value = 2
            ElseIf cell.Value = "Undetermined" Then
                cell.Offset(0, 8).Value = 3
            Else: cell.Offset(0, 8).Value = 4
            End If
        Next cell
Next ws

End Sub
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,411
Office Version
  1. 2016
Platform
  1. Windows
The ws loops just fine and I believe the syntax is correct.

I put Debug.Print ws.Name and all the ws name turned up. So, not sure what was wrong with your Excel
 

arg123

New Member
Joined
Jun 8, 2016
Messages
21
The ws loops just fine and I believe the syntax is correct.

I put Debug.Print ws.Name and all the ws name turned up. So, not sure what was wrong with your Excel
I'll keep messing with it. Thank you for your time in taking a look! Cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
At the moment your range is set to whatever sheet was active when you ran the code, try it like
VBA Code:
For Each cell In ws.Range("I2:I27,I32:I65")
 
Solution

arg123

New Member
Joined
Jun 8, 2016
Messages
21

ADVERTISEMENT

At the moment your range is set to whatever sheet was active when you ran the code, try it like
VBA Code:
For Each cell In ws.Range("I2:I27,I32:I65")
It's the little things. This type of instance is when you know you've been looking at it for too long. Thank you for your observation. That was the fix. Cheers!
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Also try counting the number of worksheets in the workbook, then changing the loop over this range, and using the Dim variable in the Sheet name to active it. Should force it to the correct sheet on each iteration of the loop...

So say worksheet total (WST) = 10

add code for counting worksheet total (WST)...

Dim i as Long

For i = 1 to WST
Sheets(i)activate

For Each cell In rnumrange

<all your stuff>

Next cell
Next i


Sorry about being vague, no time to code it atm.... trying to get to bed :)
 

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
It's the little things. This type of instance is when you know you've been looking at it for too long. Thank you for your observation. That was the fix. Cheers!
Agreed...We were addressing the same thing as he's original stated issue was the outer ws loop not moving between worksheets... because the next ws was never being referenced in some fashion. Your fix is much more tidier and easier to tweak with his original code. Thanks!
 

Forum statistics

Threads
1,136,954
Messages
5,678,758
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top