How to Skip Over Macro Based on Cell Value

[NYFashionToGo]

I have been looking through the site for some infomation regarding something I would like to execute.

I have a series of different macros Code in Excel. Each one formats a different shipping label for me. Sometimes I do not have a specific service to ship with and I would like to skip over that Macro based on a Cell Value. If a specific cell Value is >1 Run the macro if It is Zero Skip Over and Run the next one.

I would like to run all six seperate macros with one master shipping macro skipping over certain ones based on the value of a specific cell. Is this possible to do? Any leads on how to approach this would be helpful.. A term or word to look up a function... I am still a bit new to the world of VBA although I have made alot of progress in last 6 months....

Thanks in advance..

 

[SkipVought]

???

If [i]expression[/i] Then
  'what happens if [i]expression[/i] is TRUE
Else
  'what happens if [i]expression[/i] is FALSE
End if

Skip,

_[red][/red]

 

[mintjulep]

Or if your triggers are spreadsheet cells, and you want to run (on not) your macro as a UDF.

=IF(A1>1,mymacro(),IF(A1=0,"",whatever you want to happen if A1 is 1 or negative))

 

[NYFashionToGo]

You can run a macro from a cell? I was not aware of that. I will have to try that.




I managed to get what I wanted to do working using something like this. based on what skip told me.

Thanks for the responces. I appreciate it...

Sub Ship1()
If Range("A1").Value > 0 Then
Application.Run "Shipping!Ship1"
Application.Run "Shipping!Ship2"
Else
Application.Run "Shipping!Ship2"
End If
End Sub

 

[SkipVought]

1. This is the VBA forum, so spreadsheet expressions are not addressed here.

2. A UDF cannot change the value in a cell other than the cell it is in. So whatever you want to happen if A1 is 1 or negative is restricted by that factor.

Skip,

_[red][/red]

 

[mintjulep]

Um,

Isn't NYFashion's code going to result in an endless loop for A1>1 unless the ship2 procedure changes the value of A1?

 

[NYFashionToGo]

Hi Mint I understand what you are saying, However The ship 2 macro does not read off cell A1 It would read off A2. So it will end.

Basicall in cell a1 is the results
Cell a1 Priority Items to ship =10
Cell a2 Express Items to ship = 0
Cell a3 global priority to ship = 1
Cell a4 Airmail Letter Post = 0

I need to develop an XML feed for each one of the shipping options. and when I do it with a zero to ship Value it gives me issues.... trying to work my way around it by doing this...

 

[NYFashionToGo]

The whole thing should look something like this. I am sure there are easier ways to achieve it.. I am still rather new and trying to figure my way around things still.....

This whole thing should avoid exporting XML for the Express labels and Avoid the Airmail Post because the value is 0 and will not run it.....



Sub Ship1()
If Range("A1").Value > 0 Then
Application.Run "Shipping!Ship1"
Application.Run "Shipping!Ship2"
Else
Application.Run "Shipping!Ship2"
End If
End Sub



Sub Ship2()
If Range("A2").Value > 0 Then
Application.Run "Shipping!Ship2"
Application.Run "Shipping!Ship3"
Else
Application.Run "Shipping!Ship3"
End If
End Sub



Sub Ship3()
If Range("A3").Value > 0 Then
Application.Run "Shipping!Ship3"
Application.Run "Shipping!Ship4"
Else
Application.Run "Shipping!Ship4"
End If
End Sub



Sub Ship4()
If Range("A4").Value > 0 Then
Application.Run "Shipping!Ship4"
Application.Run "Shipping!Ship5"
Else
Application.Run "Shipping!Ship5"
End If
End Sub

 

[NYFashionToGo]

Actually I made a mistake when I typed the above code, I am aware of it... I do have a sample here that is working that i played around with this afternoon. Above is not right.. I know it..

 

[SkipVought]

What events execute Ship1, Ship2, Ship3 and Ship4?

Skip,

_[red][/red]

 

[NYFashionToGo]

Hi Skip this is a more accurate sequence of what I am trying to do..

I just slapped together some quick meaningless macros to represent each xml sequence.... Still working on building the actual code for that and working on the XML feeds and mapping.... The whole process starts with several different database queries from access. Which has the information all set up.....

The ship1 sub is the Master It all starts from there with a button click..

it reads a number off cell A1, A2, A3, A4 Each having a total number of shipments needed to be made by that specific service.. If the number is 0 then it skips over it and runs the next.....


Each service is in its own sub to execute. In this case the meaningless subs that I just made inside this.



Sub Ship1()

Columns("D:G").Select
Selection.ClearContents
Range("E5").Select


If Range("A1").Value > 0 Then
Application.Run "shipping.xls!Shipping1"
Application.Run "shipping.xls!Ship2"
Else
Application.Run "shipping.xls!Ship2"
End If
End Sub



Sub Ship2()
If Range("A2").Value > 0 Then
Application.Run "shipping.xls!Shipping2"
Application.Run "shipping.xls!Ship3"
Else
Application.Run "shipping.xls!Ship3"
End If
End Sub



Sub Ship3()
If Range("A3").Value > 0 Then
Application.Run "shipping.xls!Shipping3"
Application.Run "shipping.xls!Ship4"
Else
Application.Run "shipping.xls!Ship4"
End If
End Sub



Sub Ship4()
If Range("A4").Value > 0 Then
Application.Run "shipping.xls!Shipping4"

Else

End If
End Sub
Sub Shipping1()

Range("D1").Select
ActiveCell.FormulaR1C1 = "Ship1Macro"
Range("D1").Select
Selection.Copy
Range("D121").Select
ActiveSheet.Paste
ActiveSheet.Paste

End Sub
Sub Shipping2()


Range("E1").Select
ActiveCell.FormulaR1C1 = "Ship2Macro"
Range("E1").Select
Selection.Copy
Range("E1:E21").Select
ActiveSheet.Paste
End Sub
Sub shipping3()


Range("F1").Select
ActiveCell.FormulaR1C1 = "Ship3Macro"
Range("F1").Select
Selection.Copy
Range("F1:F21").Select
ActiveSheet.Paste
End Sub
Sub shipping4()


Range("G1").Select
ActiveCell.FormulaR1C1 = "Ship4Macro"
Range("G1").Select
Selection.Copy
Range("G1:G21").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub

 

[NYFashionToGo]

Whats really happening here is. Dazzle (The shipping Program) scans the specific directory for XML files. If it finds XML Files it automatically begins to print the labels.... If I send and XML file with no data inside it.. It still reads it and tries to run it..... After it runs the XML it turns into .BAK extention.

So I am trying to skip over the whole export XML files from excel.... If I dont need to use it.....

 

[SkipVought]

Isn't that something like this?

Sub test()
    With Sheets("Sheet1")
        .Columns("D:G").ClearContents
        If .Range("A1").Value > 0 Then _
            .Range("D1:D21").Value = "Ship1Macro"
        If .Range("A2").Value > 0 Then _
            .Range("E1:E21").Value = "Ship2Macro"
        If .Range("A3").Value > 0 Then _
            .Range("F1:F21").Value = "Ship3Macro"
        If .Range("A4").Value > 0 Then _
            .Range("G1:G21").Value = "Ship4Macro"
    End With
End Sub

Skip,

_[red][/red]

 

[NYFashionToGo]

I suppose it is isnt it......... Grrrrrrrr.. I lack the experience skip, I am trying very hard trying to make things work they way I invision them to work.... I am taking the scenic route and you got the express route ...right in front of me....

Your very good, I have read alot of your posts throughout the board.. You must have lot of patience to see people with limited ability (as I do) asking very simple questions....

I get get stuck sometimes, I lack the experience and education...

Thank you for your help.. I appreciate that...

Chris