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

arg123

New Member
Joined
Jun 8, 2016
Messages
28
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
Solution
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!
 
Upvote 0
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 :)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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
Back
Top