Loop Through all Worksheets, Change cell Value based on another conditional cell

[Spswytch]

I know its something dumb im missing.

All I need to do is scan a large range in column "A" I.E. A1:A524, If the Cell in Column A matches a Number in the Case Statement is Fills in the Adjacent Cell in Column "B" with the Text from the case statement. There are 40+ Worksheets in the Workbook. I can get the Following to work for the 1st Worksheet just fine, I put a small error trap in the bottom and it does indeed flip through the worksheets one at a time as evidenced by the Pop up windows showing each worksheet as it flips through them, but only ever changes the 1st worksheet. What am I Missing?



Sub ProcessAllWorksheets()
Dim wk As Worksheet
Dim Target As Range
For Each wk In ActiveWorkbook.Worksheets

For Each Target In Range("A1:A5")
Select Case Target.Value
Case "21"
Me.Range("B" & Target.Row).Value = "FTP Service Port"
Case "22"
Me.Range("B" & Target.Row).Value = "SSH Management Port"
Case "123"
Me.Range("B" & Target.Row).Value = "Network Time Protocol"
Case "2207"
Me.Range("B" & Target.Row) = "Python"
End Select
Next Target
MsgBox wk.Name
Next wk
End Sub

[MarvinP]

Hi Spswytch and welcome to the forum,

Code works on the active sheet. You simply need to make the next sheet active with a Wk.Select statement just below your first For line.

If that doesn't do it for you, keep asking.

[Spswytch]

Nevermind I got it.

It was the Me.Range statements, should be wk.range. changed it works fine

[Spswytch]

Thanks for your reply, Still no go. Although with your line added it does activate each sheet, but doesnt run through the second for loop nor change cell values in sheet 2 or 3

[MarvinP]

Hi Spswytch,

You had your numbers in quotes. That was one problem. Another might have been you were only checking the first 5 rows. See if this works for you.

Sub ProcessAllWorksheets()
Dim wk As Worksheet
Dim Target As Range
    For Each wk In ActiveWorkbook.Worksheets
    wk.Select
    For Each Target In Range("A1:A524")
        Select Case Target.Value
            Case 21
                Range("B" & Target.Row).Value = "FTP Service Port"
            Case 22
                Range("B" & Target.Row).Value = "SSH Management Port"
            Case 123
                Range("B" & Target.Row).Value = "Network Time Protocol"
            Case 2207
                Range("B" & Target.Row) = "Python"
        End Select
    Next Target
        'MsgBox wk.Name
    Next wk
End Sub

[Spswytch]

I had only given the 1st 5 Case statements as an example, im not going to list out the 1500 ports in this post that seemed pointless. As for the Numbers in Quotes, The Column is Type General not Type Number safer to use the quotes. It works I just had to change where it was looking for the Range in the worksheets. The Following worked for what i needed it to.

Sub ProcessAllWorksheets()
Dim wk As Worksheet
Dim Target As Range
For Each wk In ActiveWorkbook.Worksheets

For Each Target In Range("A1:A5")

Select Case Target.Value
Case "21"
Me.Range("B" & Target.Row).Value = "FTP Service Port"
Case "22"
wk.Range("B" & Target.Row).Value = "SSH Management Port"
Case "123"
wk.Range("B" & Target.Row).Value = "Network Time Protocol"
Case "2207"
wk.Range("B" & Target.Row) = "Python"
End Select
Next Target
MsgBox wk.Name
Next wk
End Sub

Thank you all for your advice. It put me on the right path greatly appreciated.