# To write a function for a series of if statements

#### nynamyna

##### New Member
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Search for CASE SELECT statements, they tend to be a better way to deal with conditions that have more than two possible outcomes

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:
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 ?

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
Application.ScreenUpdating = True
End Sub``````

just curious...why should we do this ?

Code:
``````Sub YourMacro()
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End Sub``````

Last edited:
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.

Replies
7
Views
474
Replies
6
Views
390
Replies
2
Views
330
Replies
0
Views
331
Replies
4
Views
672

1,217,505
Messages
6,137,030
Members
450,041
Latest member
MM2024

### 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.

### Which adblocker are you using?

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

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