To write a function for a series of if statements

nynamyna

New Member
Joined
Jul 12, 2009
Messages
34
Hello everybody,

In the following code I call a series of if statements again and again under different conditions. For example I have highlighted one set of those if statements. They are repeated again and again in the code under different if conditions. Is there a way to write a seperate function for this and call it ? Is thee an efficient way to write these statements ?

Rich (BB code):
LastRow = Sheets(2).UsedRange.Rows.count
For iCell = 2 To LastRow
    If Sheets(2).Range("B" & iCell).Value <> Sheets(2).Range("B" & (iCell - 1)).Value Then
            If Sheets(2).Range("E" & iCell).Value > 0 Then
 
            If Sheets(2).Range("E" & iCell).Value <= Sheets(2).Range("K" & iCell).Value Then
               O_FullCov = O_FullCov + 1
 
               If Sheets(2).Range("C" & iCell).Value > 0.75 Then
                 Sheets(1).Range("D24").Value = Sheets(1).Range("D24").Value + 1
               End If
 
               If Sheets(2).Range("C" & iCell).Value <= 0.75 Then
                   If Sheets(2).Range("C" & iCell).Value > 0.5 Then
                    Sheets(1).Range("E24").Value = Sheets(1).Range("E24").Value + 1
                   End If
               End If
 
               If Sheets(2).Range("C" & iCell).Value > 0.25 Then
                    If Sheets(2).Range("C" & iCell).Value <= 0.5 Then
                      Sheets(1).Range("F24").Value = Sheets(1).Range("F24").Value + 1
                    End If
               End If
 
              If Sheets(2).Range("C" & iCell).Value > 0 Then
                  If Sheets(2).Range("C" & iCell).Value <= 0.25 Then
                   Sheets(1).Range("G24").Value = Sheets(1).Range("G24").Value + 1
                  End If
               End If
 
               If Sheets(2).Range("C" & iCell).Value = 0 Then
                   Sheets(1).Range("H24").Value = Sheets(1).Range("H24").Value + 1
               End If
                
            ElseIf Sheets(2).Range("I" & iCell).Value = 0 Then
                O_NoCov = O_NoCov + 1
 
                If Sheets(2).Range("C" & iCell).Value > 0.75 Then
                 Sheets(1).Range("D28").Value = Sheets(1).Range("D28").Value + 1
                End If
 
                If Sheets(2).Range("C" & iCell).Value <= 0.75 Then
                    If Sheets(2).Range("C" & iCell).Value > 0.5 Then
                     Sheets(1).Range("E28").Value = Sheets(1).Range("E28").Value + 1
                    End If
                End If
 
                If Sheets(2).Range("C" & iCell).Value > 0.25 Then
                     If Sheets(2).Range("C" & iCell).Value <= 0.5 Then
                     Sheets(1).Range("F28").Value = Sheets(1).Range("F28").Value + 1
                     End If
                End If
 
               If Sheets(2).Range("C" & iCell).Value > 0 Then
                   If Sheets(2).Range("C" & iCell).Value <= 0.25 Then
                   Sheets(1).Range("G28").Value = Sheets(1).Range("G28").Value + 1
                   End If
                End If
 
                If Sheets(2).Range("C" & iCell).Value = 0 Then
                     Sheets(1).Range("H28").Value = Sheets(1).Range("H28").Value + 1
                End If
 
            Else
                O_SomeCov = O_SomeCov + 1
 
                If Sheets(2).Range("C" & iCell).Value > 0.75 Then
                 Sheets(1).Range("D26").Value = Sheets(1).Range("D26").Value + 1
                End If
 
                If Sheets(2).Range("C" & iCell).Value <= 0.75 Then
                    If Sheets(2).Range("C" & iCell).Value > 0.5 Then
                    Sheets(1).Range("E26").Value = Sheets(1).Range("E26").Value + 1
                    End If
                End If
 
                If Sheets(2).Range("C" & iCell).Value > 0.25 Then
                     If Sheets(2).Range("C" & iCell).Value <= 0.5 Then
                     Sheets(1).Range("F26").Value = Sheets(1).Range("F26").Value + 1
                     End If
                End If
 
               If Sheets(2).Range("C" & iCell).Value > 0 Then
                   If Sheets(2).Range("C" & iCell).Value <= 0.25 Then
                    Sheets(1).Range("G26").Value = Sheets(1).Range("G26").Value + 1
                   End If
                End If
 
                If Sheets(2).Range("C" & iCell).Value = 0 Then
                    Sheets(1).Range("H26").Value = Sheets(1).Range("H26").Value + 1
                End If
 
             End If
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Search for CASE SELECT statements, they tend to be a better way to deal with conditions that have more than two possible outcomes
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try this out, it uses Application.Lookup to determine the column since there is good consistency in your requirements:

Code:
Public Sub quickerIF()
Dim col As Variant
LastRow = Sheets(2).UsedRange.Rows.Count
For iCell = 2 To LastRow
    If Sheets(2).Range("B" & iCell).Value <> Sheets(2).Range("B" & (iCell - 1)).Value Then
        If Sheets(2).Range("E" & iCell).Value > 0 Then
            col = Application.Lookup(Sheets(2).Range("C" & iCell).Value - 0.0000000001, Array(-9.99E+307, 0, 0.25, 0.5, 0.75), Array(8, 7, 6, 5, 4))
            If Sheets(2).Range("E" & iCell).Value <= Sheets(2).Range("K" & iCell).Value Then
                O_FullCov = O_FullCov + 1
                Sheets(1).Cells(24, col).Value = Sheets(1).Cells(24, col).Value + 1
            ElseIf Sheets(2).Range("I" & iCell).Value = 0 Then
                O_NoCov = O_NoCov + 1
                Sheets(1).Cells(28, col).Value = Sheets(1).Cells(28, col).Value + 1
            Else
                O_SomeCov = O_SomeCov + 1
                Sheets(1).Cells(26, col).Value = Sheets(1).Cells(26, col).Value + 1
            End If
        End If
    End If
Next iCell
End Sub
 
Last edited:

nynamyna

New Member
Joined
Jul 12, 2009
Messages
34
It worked....you are awesome....I have one more problem ....I am setting these values to zero before updating.

Code:
Sheets(1).Range("D24").Value = 0
Sheets(1).Range("E24").Value = 0
Sheets(1).Range("F24").Value = 0
Sheets(1).Range("G24").Value = 0
Sheets(1).Range("H24").Value = 0
Sheets(1).Range("D25").Value = 0
Sheets(1).Range("E25").Value = 0
Sheets(1).Range("F25").Value = 0
Sheets(1).Range("G25").Value = 0
Sheets(1).Range("H25").Value = 0
Sheets(1).Range("D26").Value = 0
Sheets(1).Range("E26").Value = 0
Sheets(1).Range("F26").Value = 0
Sheets(1).Range("G26").Value = 0
Sheets(1).Range("H26").Value = 0
Sheets(1).Range("D27").Value = 0

Can you help me to avoid doing this


Since I am updating the cell values it is taking little longer. is tere a way to make it better ?
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Perhaps:

Code:
Sheets(1).Range("D24:H27").Value = 0

Also, be sure you are wrapping your code with Application.ScreenUpdating = False and Application.ScreenUpdating = True lines:

Code:
Sub YourMacro()
Application.ScreenUpdating = False
'Your Code Here
Application.ScreenUpdating = True
End Sub
 

nynamyna

New Member
Joined
Jul 12, 2009
Messages
34
just curious...why should we do this ?

Code:
Sub YourMacro()
Application.ScreenUpdating = False
'Your Code Here
Application.ScreenUpdating = True
End Sub
 
Last edited:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Any time excel changes a cell value, adds/deletes rows, or really anything that would otherwise change what you see on the screen, the application has to refresh the screen so that you can see the changes. This updating is actually a relatively slow process. By stopping Excel from updating the screen with every change, we can shave (potentially) minutes off of the time it takes for a macro to run. I've had some really hefty macros that by merely adding in those lines, it took a 10 min macro down to 30 seconds.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

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