Nesting If statments with variable and Statements

newclasshero

New Member
Joined
Sep 1, 2006
Messages
46
What this macro does is Prints off all my sheets in a certain order according to who I bought my vehicle from. I have recently added a sheet that only needs to be printed off if the Make (cell C6) is not a General Motors Product.

Also if there is a way to nest multiple Vehicle makes into the code (ie. if cell C6 =Ford, Lexus, Chrysler etc. etc.)

So basically if VIS G4 = Brian and VIS C6 = "Ford, Lexus, Chrysler etc. etc.) then

*Start of code*


Here is my current Code

If Sheets("V I S").Range("G4") = "BRYAN" Then

Sheets("Endsheet").PrintOut From:=1, To:=1
Sheets("V I S").PrintOut From:=1, To:=1
Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=1, To:=1
Sheets("Endsheet").PrintOut From:=8, To:=8
Sheets("FAX C.B. - OCI").PrintOut From:=5, To:=5
Sheets("Fax Dealer").PrintOut From:=4, To:=4
Sheets("Fax Dealer").PrintOut From:=3, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
Are you sure it's and you want?

Wouldn't it be a combination of or/and, or even a select case?
Code:
If If Sheets("V I S").Range("G4").Value = "BRYAN" Then 
   Select Case Sheets("V I S").Range("G4").Value
         Case "Ford", "Lexus", "Chrysler"
               Sheets("Endsheet").PrintOut From:=1, To:=1
               Sheets("V I S").PrintOut From:=1, To:=1
               Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
               Sheets("FAX C.B. - OCI").PrintOut From:=1, To:=1
               Sheets("Endsheet").PrintOut From:=8, To:=8
               Sheets("FAX C.B. - OCI").PrintOut From:=5, To:=5
               Sheets("Fax Dealer").PrintOut From:=4, To:=4
               Sheets("Fax Dealer").PrintOut From:=3, To:=3
               Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
               Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4
         Case Else
               ' Do something Else
   End Select
End If
 

newclasshero

New Member
Joined
Sep 1, 2006
Messages
46
Here is what I have in it's entirety.

The problem now is that when I hit Cntrl+Shift+P it runs the Case Select properly but then also executes the Else Satment and prints all of those pages too.

Sub Print_All()
'
' Print_All Macro
' Prints all necessary pages of IVIS.
'
' Keyboard Shortcut: Ctrl+Shift+P
'
If Sheets("V I S").Range("G4").Value = "BRYAN" Then
Select Case Sheets("V I S").Range("C6").Value
Case "Ford", "Lincoln", "Toyota", "Lexus", "BMW", "Mitsubishi"
Sheets("Endsheet").PrintOut From:=1, To:=1
Sheets("V I S").PrintOut From:=1, To:=1
Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=1, To:=1
Sheets("Endsheet").PrintOut From:=8, To:=8
Sheets("FAX C.B. - OCI").PrintOut From:=6, To:=7
Sheets("Fax Dealer").PrintOut From:=4, To:=4
Sheets("Fax Dealer").PrintOut From:=3, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4

Case Else
Sheets("Endsheet").PrintOut From:=1, To:=1
Sheets("V I S").PrintOut From:=1, To:=1
Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=1, To:=1
Sheets("Endsheet").PrintOut From:=8, To:=8
Sheets("FAX C.B. - OCI").PrintOut From:=5, To:=5
Sheets("Fax Dealer").PrintOut From:=4, To:=4
Sheets("Fax Dealer").PrintOut From:=3, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4
End Select

If Sheets("V I S").Range("G4") = "EXECUTIVE" Then
Sheets("Endsheet").PrintOut From:=1, To:=1
Sheets("V I S").PrintOut From:=1, To:=1
Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
Sheets("Endsheet").PrintOut From:=5, To:=5
Sheets("FAX C.B. - OCI").PrintOut From:=5, To:=5
Sheets("Fax Dealer").PrintOut From:=4, To:=4
Sheets("Fax Dealer").PrintOut From:=3, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4

Else
Sheets("Endsheet").PrintOut From:=1, To:=1
Sheets("V I S").PrintOut From:=1, To:=1
Sheets("FAX Currency-Tag").PrintOut From:=1, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=1, To:=1
Sheets("Fax Dealer").PrintOut From:=1, To:=2
Sheets("Endsheet").PrintOut From:=5, To:=5
Sheets("FAX C.B. - OCI").PrintOut From:=5, To:=5
Sheets("Fax Dealer").PrintOut From:=4, To:=4
Sheets("Fax Dealer").PrintOut From:=3, To:=3
Sheets("FAX C.B. - OCI").PrintOut From:=2, To:=2
Sheets("FAX Currency-Tag").PrintOut From:=4, To:=4
End If
End Sub


Any further help would be appreciated.

ps. if there is any way to simplify this macro into something that is less cumbersome it would be nice to know about that too.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What in plain English is the code meant to do?

What 'else' part is running that you don't want to run?
 

newclasshero

New Member
Joined
Sep 1, 2006
Messages
46
Sorry about that.

What the code does is print off my workbook pages in the different a different order depending on who ("Brian", "Executive", "Everyone Else") I have purchased the vehicle from.

In addition, I am using the Select Case function to get the Macro to print a slightly different print order with the addition of one page. All of my vehicles need to have the speedometer converted to KM from miles. If it is a non-GM make (ie. Ford, Lincoln, etc) then I need a different sheet to print off.

The Else part that is running is 4th set of code.

1. IF...."BRYAN" then
Select Case ("Ford, Lincoln")
Code

2. Case Else
Code

3. IF.... "EXECUTIVE" then
Code

4. Else
Code
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
Sorry but the logic still isn't clear, to me anyway.:eek:

I realise you want to do a different print out based on certain criteria but what that criteria isn't clear.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,429
Members
409,876
Latest member
Akash Yadav
Top