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

[arg123]

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:

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

 

[Zot]

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]

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]

At the moment your range is set to whatever sheet was active when you ran the code, try it like

For Each cell In ws.Range("I2:I27,I32:I65")

 

[arg123]

At the moment your range is set to whatever sheet was active when you ran the code, try it like

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]

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

 

[Fluff]

That was the fix. Cheers!

You're welcome & thanks for the feedback.

@Oddball2020 It's always best to avoid selecting things unless you absolutely need to.

 

[Oddball2020]

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!