Looping IF/ELSE through all worksheets

[danelskibr]

Hello,

I am trying to run a loop through all of the worksheets in my workbook. I have around 100 sheets in the workbook, but they are each are formatted one of only two ways. For test purposes only, I have the macro putting either a "1" or a "2" in cell A1 depending on which type of sheet it is (if K3 is blank).

I found the following code on Microsoft's website and modified it slightly for my use. The macro works, but it will only run for the active worksheet and stop. Basically, it is not looping.

Sub Format()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count
         
         
   If IsEmpty(Range("K3").Value) = True Then
   
Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    
    Else
    
     Range("A1").Select
    ActiveCell.FormulaR1C1 = "2"
   
   End If
   
   
         Next I
         
End Sub

Thank you in advance for the help!

 

[rafonga]

In your code, you did not set the loop to go through the Worksheets. Try this:

Sub Format()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count


If IsEmpty(Range("K3").Value) = True Then

Worksheets(I).Range("A1") = 1

Else

Worksheets(I).Range("A1") = 2

End If


Next

End Sub

 

[Rick Rothstein]

Here is another way to write the macro...

Sub Formats()
  Dim WS As Worksheet
  For Each WS In Worksheets
    WS.Range("A1") = 2 + (WS.Range("K3") = "")
  Next
End Sub

Note: I changed the macro name from Format to Formats... Format is the name of a built-in VB function and should not be used as a procedure name.

 

[rafonga]

Actually, try this one:

Sub Format()


Dim WS_Count As Integer
Dim I As Integer


WS_Count = ActiveWorkbook.Worksheets.Count


For I = 1 To WS_Count

If Worksheets(I).Range("K3") = "" Then
Worksheets(I).Range("A1") = 1
Else
Worksheets(I).Range("A1") = 2
End If

Next

End Sub

 

[rafonga]

Rick, what does this line do ?
WS.Range("A1") = 2 + (WS.Range("K3") = "")

I mean, what is the value of (WS.Range("K3") = "")

 

[Rick Rothstein]

Rick, what does this line do ?
WS.Range("A1") = 2 + (WS.Range("K3") = "")

I mean, what is the value of (WS.Range("K3") = "")

(WS.Range("K3") = "") is a logical expression (basically, it is what one would put in an IF..Then statement surrounded by parentheses to keep its evaluation isolated from the rest of the code. As a logical expression, it will return either True or False depending on whether cell K3 is blank or not. We then involve it in a mathematical expression and VB automatically converts the True or False value to an number for use in the calculation. In vb (and unlike in an Excel formula), True evaluates to minus one (-1) and False to 0. So, when the logical expression is converted to a number during the evaluation of the code line it's in, we will be assigning to cell A1 either of these (depending on the truthfulness of the logical expression)...

2 + True => 2 -1 => 1

2 + False => 2 + 0 => 2

and those are the two values the OP wants assigned to the cell A1 depending on if K3 is blank or not.

 

[rafonga]

Thanks Rick, I guessed so, but True evaluated to -1 was hard to believe kkk

 

[Rick Rothstein]

...but True evaluated to -1 was hard to believe kkk

Although VB considers any non-zero number to be True, actual Boolean values are defined as False equals 0... that is, no bits set... and True equal to FFFFFFFF (for a 32 bit system)... that is, all bits set. If you convert the Hex value FFFFFFFF to a decimal number in a system using signed Hex values (like VB), it equals -1 (try printing &HFFFFFFFF in the Immediate Window to see this)... VB simply uses that value directly.

 

[danelskibr]

Thank you both for the help! The code worked perfectly. However, I changed the formatting function for the first IF, and it no longer works. I am starting to think that maybe I should have approached this differently. When I changed the line, that portion of the code no longer functioned correctly. The second part of the statement (the part that adds a "2" in A1) still works fine.

My goal is to run two sets of code that will format all of my sheets in one of two ways. All the sheets are either formatted in style "A" or in style "B", so I thought this would be an easy way to accomplish this. Do either of you have a better or more effective way of accomplishing this?


Basically something like:

If Sheet A, run formatting code for Sheet A style.

If not Sheet A, Run formatting code for Sheet B style.

Move to next sheet and repeat for the entire workbook

Sub Formater()


 Dim WS_Count As Integer
 Dim I As Integer


 WS_Count = ActiveWorkbook.Worksheets.Count


 For I = 1 To WS_Count

'formating for all the "- F" sheets
 If Worksheets(I).Range("K3") = "" Then
 'CHANGED
 Rows("4:6").Select
    Selection.Delete Shift:=xlUp
 
 
 
'formating for all the "- T" sheets
 Else
 Worksheets(I).Range("A1") = 2
 
 
 End If

 Next

 End Sub