End all

DaK

New Member
Joined
Apr 22, 2002
Messages
16
I have several subs that run in a chain. If the first sub meets a certain criteria then it skips to the next sub and so on. There are five subs in all.

The problem: When it finishes running the last sub, in starts backtracking to the previous sub until it gets back to the first sub. This creates a long running procedure. (does this make any sense to anyone?)

What I need it to do when it runs the last sub is to simply stop running all of the subs instead of backtracking.

Let me post the code to hopefully clear things up. Maybe you can even give me some ideas on how to clean this code up!!! (p.s. this code is only a begining, there will be more added to it as you will see by the first "if" statement) Here it is: Thanks in advance to anyone that can help.


Private Sub cmdSearch_Click()

Dim x As Integer
Dim i As Integer

If cmbBrand.Value = "Etch Art" Then
Workbooks("Etch_Art_Inventory.xls").Activate
End If

If txtItem1.Value = "" Then
run2
End If

Application.ScreenUpdating = False

x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem1.Value Then
lblOnhand1.Caption = Range("k" & x)
lblOnorder1.Caption = Range("l" & x)
lblAvailable1.Caption = Range("m" & x)
run2
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub

Private Sub run2()

If txtItem2.Value = "" Then
run3
End If

Dim x As Integer
Dim i As Integer

Application.ScreenUpdating = False

x = 3
For i = 3 To 50
If txtItem2.Value = "" Then
Exit Sub
End If

Range("b" & x).Select
If Range("b" & x) = txtItem2.Value Then
lblOnhand2.Caption = Range("k" & x)
lblOnorder2.Caption = Range("l" & x)
lblAvailable2.Caption = Range("m" & x)
run3
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub

Private Sub run3()

If txtItem3.Value = "" Then
run4
End If

Dim x As Integer
Dim i As Integer

Application.ScreenUpdating = False

x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem3.Value Then
lblOnhand3.Caption = Range("k" & x)
lblOnorder3.Caption = Range("l" & x)
lblAvailable3.Caption = Range("m" & x)
run4
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub

Private Sub run4()

If txtItem4.Value = "" Then
run5
End If

Dim x As Integer
Dim i As Integer

Application.ScreenUpdating = False

x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem4.Value Then
lblOnhand4.Caption = Range("k" & x)
lblOnorder4.Caption = Range("l" & x)
lblAvailable4.Caption = Range("m" & x)
run5
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub

Private Sub run5()

If txtItem5.Value = "" Then
Exit Sub
End If

Dim x As Integer
Dim i As Integer

Application.ScreenUpdating = False

x = 3
For i = 3 To 50
Range("b" & x).Select
If Range("b" & x) = txtItem5.Value Then
lblOnhand5.Caption = Range("k" & x)
lblOnorder5.Caption = Range("l" & x)
lblAvailable5.Caption = Range("m" & x)
Workbooks("Tools_Inventory.xls").Activate
Exit Sub
Else
x = x + 1
End If
Next i
Workbooks("Tools_Inventory.xls").Activate
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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