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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
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
76,305
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
76,305
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.
 

Forum statistics

Threads
1,144,614
Messages
5,725,319
Members
422,613
Latest member
salim9696

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