SOLVED

How can I tell macro to perform operations below "= vbNo Then" ?

Iron Contributor

Sub OPTIMIZE()
If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbYes Then
Call Combo_MacroLessThan4k

"= vbNo Then" (How can I tell macro to perform operations below)

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Application.Calculation = xlAutomatic


ActiveSheet.Range("$BB$21:$BD$629").AutoFilter Field:=1

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"aw30:aw629"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B30:BA629")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End If
End Sub

9 Replies

Hey@Greg Bonaparte 

Just use an Else statement and indent what you have written that must happen when "=vbNo"

.......

Call ComboMacroLessThan4k

Else

      ActiveWindow.ScrollRow = 624

........

 

If that does not work, just use an ElseIf statement 

........

Call Combo_MacroLessThan4k

ElseIf If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNo, "") = vbNo

       ActiveWindow.ScrollRow = 624

........

 

 

 

 

@Excel_World_Champion 

Hello, Thank you for your assistence. I attempted your suggestion with the following code, adding a "cancel" option. However I get compile errors. Please examine and correct:

 

Sub OPTIMIZE()
If MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24") = vbYes Then

Call Combo_MacroLessThan4k

Else

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents
End Sub

Hey @Greg Bonaparte 

 

I think all you have got to do now is add and "End If" on a line before the "End Sub" statement

 

Thank you than Helped. But "Cancel" and "No" appear to both be running the "Call"  macro. How can I make "Cancel simply abort?

 

@Excel_World_Champion 

Hey @Greg Bonaparte 

 

So, what you have to do is to make an "ElseIf" statement for vbNo, and and "Else" statement for the when you press cancel. 

 

Here is the code I used (I just used "a" as a variable for the Messagebox):

 

Sub OPTIMIZE()

a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

If a = vbYes Then
Call Combo_MacroLessThan4k

ElseIf a = vbNo Then

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Else
Exit Sub
End If
End Sub

 

 

Thank you, Your code ran perfectly. One final question: Why do I get and "Else" Compile error when I place the following code just above your last "Else":

 

Sub OPTIMIZE()

a = MsgBox("OPTIMIZE AWSORT FOR A MINIMUM OF TWENTY-FOUR INSTRUMENTS. HAVE YOU COMPLETED OPTIMIZATION?", vbQuestion + vbYesNoCancel, "HonorSystem24")

If a = vbYes Then
Call Combo_MacroLessThan4k

ElseIf a = vbNo Then

ActiveWindow.ScrollRow = 624
Range("V13:V14").Select
Selection.Copy
Range("V640:V641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W13:W14").Select
Selection.Copy
Range("W640:W641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X13:X14").Select
Selection.Copy
Range("X640:X641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y13:Y14").Select
Selection.Copy
Range("Y640:Y641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z13:Z14").Select
Selection.Copy
Range("Z640:Z641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA13:AA14").Select
Selection.Copy
Range("AA640:AA641").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V13:Z14").Select
Selection.ClearContents

Application.Calculation = xlAutomatic


ActiveSheet.Range("$BB$21:$BD$629").AutoFilter Field:=1

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"aw30:aw629"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B30:BA629")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

 


Else
Exit Sub
End If
End Sub

@Excel_World_Champion 

best response confirmed by Greg Bonaparte (Iron Contributor)
Solution

Hey @Greg Bonaparte 

 

Its probably because you have not added an "End With" statement

Everything works perfectly. Thank you@Excel_World_Champion 

1 best response

Accepted Solutions
best response confirmed by Greg Bonaparte (Iron Contributor)
Solution

Hey @Greg Bonaparte 

 

Its probably because you have not added an "End With" statement

View solution in original post