To write a function for a series of if statements

N

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Search for CASE SELECT statements, they tend to be a better way to deal with conditions that have more than two possible outcomes
 
Upvote 0
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:
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
just curious...why should we do this ?

Code: 
Sub YourMacro()
Application.ScreenUpdating = False
'Your Code Here
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Can you use an Array within an IF statement, using AND as well?
Replies
7
Views
558
RobP
R
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
262
chazrab
C
U
  • Question Question
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
42
Skyybot
Skyybot
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
265
RoryA
RoryA
I
  • Question Question
Unable to run VBA code
Replies
6
Views
141
RoryA
RoryA

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

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