Too many ifs

O

Oldbill62

New Member
Joined
Nov 26, 2014
Messages
23
Hi all
I am sure that this is really obvious but can anyone help me reduce the number of if statements I am having to use please. The code works but there must be a better way.
Code: 
If Sheets("Download").Range("n4") = "MIDEL" Then Sheets("Report1").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n5") = "MIDEL" Then Sheets("Report2").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n6") = "MIDEL" Then Sheets("Report3").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n7") = "MIDEL" Then Sheets("Report4").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n8") = "MIDEL" Then Sheets("Report5").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n9") = "MIDEL" Then Sheets("Report6").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n10") = "MIDEL" Then Sheets("Report7").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n11") = "MIDEL" Then Sheets("Report8").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n12") = "MIDEL" Then Sheets("Report9").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n13") = "MIDEL" Then Sheets("Report10").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n14") = "MIDEL" Then Sheets("Report11").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n15") = "MIDEL" Then Sheets("Report12").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n16") = "MIDEL" Then Sheets("Report13").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n17") = "MIDEL" Then Sheets("Report14").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n18") = "MIDEL" Then Sheets("Report15").Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
If Sheets("Download").Range("n4") = "MINERAL" Then Sheets("Report1").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n5") = "MINERAL" Then Sheets("Report2").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n6") = "MINERAL" Then Sheets("Report3").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n7") = "MINERAL" Then Sheets("Report4").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n8") = "MINERAL" Then Sheets("Report5").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n9") = "MINERAL" Then Sheets("Report6").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n10") = "MINERAL" Then Sheets("Report7").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n11") = "MINERAL" Then Sheets("Report8").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n12") = "MINERAL" Then Sheets("Report9").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n13") = "MINERAL" Then Sheets("Report10").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n14") = "MINERAL" Then Sheets("Report11").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n15") = "MINERAL" Then Sheets("Report12").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n16") = "MINERAL" Then Sheets("Report13").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n17") = "MINERAL" Then Sheets("Report14").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n18") = "MINERAL" Then Sheets("Report15").Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
If Sheets("Download").Range("n4") = "SILICON" Then Sheets("Report1").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n5") = "SILICON" Then Sheets("Report2").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n6") = "SILICON" Then Sheets("Report3").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n7") = "SILICON" Then Sheets("Report4").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n8") = "SILICON" Then Sheets("Report5").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n9") = "SILICON" Then Sheets("Report6").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n10") = "SILICON" Then Sheets("Report7").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n11") = "SILICON" Then Sheets("Report8").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n12") = "SILICON" Then Sheets("Report9").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n13") = "SILICON" Then Sheets("Report10").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n14") = "SILICON" Then Sheets("Report11").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n15") = "SILICON" Then Sheets("Report12").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n16") = "SILICON" Then Sheets("Report13").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n17") = "SILICON" Then Sheets("Report14").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
If Sheets("Download").Range("n18") = "SILICON" Then Sheets("Report15").Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
Thanks in advance
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Try:
Code: 
Sub Oldbill62()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim rng As Range
    For Each rng In Sheets("Download").Range("N8:N18")
        Select Case rng.Value
            Case "MIDEL", "MINERAL", "SILICON"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
                Next x
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
mumps said:
Try:
Code: 
Sub Oldbill62()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim rng As Range
    For Each rng In Sheets("Download").Range("N8:N18")
        Select Case rng.Value
            Case "MIDEL", "MINERAL", "SILICON"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
                Next x
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
Click to expand...

Surely that wont work as it is?
There are three different results the OP wanted:

Electrical Equipment Insulating Midel Oil Analysis Report
Electrical Equipment Insulating Mineral Oil Analysis Report
Electrical Equipment Insulating Silicon Oil Analysis Report

I only see Silicon in your code
 
Upvote 0
@Special-K99: My apologies. Thank you for picking up on that. I should be a little more careful reading the post.
 
Upvote 0
mumps said:
Try:
Code: 
Sub Oldbill62()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim rng As Range
    For Each rng In Sheets("Download").Range("N8:N18")
        Select Case rng.Value
            Case "MIDEL", "MINERAL", "SILICON"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
                Next x
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hi Mumps
Thanks for really speedy response but I'm afraid Special -k99 is correct and only the silicone option is given, any further ideas would be much appreciated
Cheers
Oldbill
 
Upvote 0
Try:
Code: 
Sub Oldbill62()
    Application.ScreenUpdating = False
    Dim x As Long
    Dim rng As Range
    For Each rng In Sheets("Download").Range("N8:N18")
        Select Case rng.Value
            Case "MIDEL"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Midel Oil Analysis Report"
                Next x
            Case "MINERAL"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Mineral Oil Analysis Report"
            Case "SILICON"
                For x = 1 To 15
                    Sheets("Report" & x).Range("A11") = "Electrical Equipment Insulating Silicon Oil Analysis Report"
                Next x
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
@Special-K99: My apologies. Thank you for picking up on that. I should be a little more careful reading the post.
Click to expand...

Thats Ok, Im no VBA expert.
Also the range should be n4:n18

Isn't this just a case (no pun intended) of inserting the value of the x variable into the output string?

My main concern is the OPs original post.
If n4-n18 contains a mixture of MIDEL MINERAL and SILICON then the result will be SILICON everywhere since ALL the conditions get executed (there are no ELSEs in the code).
ie consider the following n4 is MIDEL n5 is MINERAL n6 is SILICON
n4 is MIDEL so the MIDEL gets written everywhere
However now n5 gets tested. Since its MINERAL the value MINERAL will get written over the top of MIDEL.
And since n6 is SILICON now SILICON will get written over the top of MINERAL.
The implication is SILICON takes precedence over MIDEL and MINERAL
and that
MINERAL takes precedence over MIDEL.

This sounds dubious to me.

However this scenario is fine if the range n4-n18 contains the same value, but in that case you only need to test n4 since n5-n18 will be the same.
 
Upvote 0
Hi Mumps
Sorry but still not acheiving what I was looking for. To clarify
If range N4 = MIDEL then cell A11 in sheet report 1 will equal "Electrical Equipment Insulating Midel Oil Analysis Report"
If range N5 =SILICON then cell A11 in sheet report 2 will equal "Electrical Equipment Insulating Silicon Oil Analysis Report"
etc.
I hope this makes sense
Regards
Oldbill
 
Upvote 0
The OPs original code suggests a precedence of SILICON over MIDEL/MINERAL and MINERAL over MIDEL.

The OP needs to verify with us first if there is any precedence involved at all.

@Oldbill62:

Your original code is saying
if n4-n18 contains MIDEL then Report1-15 MIDEL Oil Analysis Report
but if n4-n18 contains MINERAL overwrite what we've just produced (MIDEL Oil Analysis Report) with MINERAL Oil Analysis Report
followed by
if n4-n18 contains SILICON MINERAL overwrite what we've just done (MINERAL Oil Analysis Report) with SILICON Oil alysis Report

Is this what you want to happen? SILICON takes precedence over MIDEL and MINERAL
and MINERAL takes precedence over MIDEL?
 
Last edited:
Upvote 0
Hi Special-k99
Further to my explanation I will try to further clarify . the number of the row containing the MIDEL, MINERAL, SILICON dictates which sheet the resulting statement appears in cell A11
eg. If N12 =MINERAL then Report 9 cell A11 will say "Electrical Equipment Insulating Mineral Oil Analysis Report"
And so on
I hope this helps
Cheers for the interest guys
Oldbill
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Too many lines of code
2
Replies
12
Views
1K
ukmikeb
U

Forum statistics

Threads
1,215,471
Messages
6,124,999
Members
449,201
Latest member
Lunzwe73

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