Thread: how to call subprocedure from within procedure?
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
dreamz[_13_] dreamz[_13_] is offline
external usenet poster
  
Posts: 1
Default how to call subprocedure from within procedure?

Leith Ross Wrote:
Hello dreamz,

When you say stops, did the call to the proccedure not execute, or did
it generate an error? You should also post the code for Calculate.

Thanks,
Leith Ross
thanks for the reply. when i say that it stops, i mean that the
procedure gets executed until the very end, but it doesn't call the
"calculate" procedure. no error at all.

here's the code for the part that calls. i've had to edit it for
security reasons.


Code:
--------------------

'Pull data from listWest
shTemp.Range("C1").Select
For i = 0 To listWest.ListCount - 1
If listWest.Selected(i) Then ActiveCell.Value = listWest.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Pull data from listCentral
shTemp.Range("E1").Select
For i = 0 To listCentral.ListCount - 1
If listCentral.Selected(i) Then ActiveCell.Value = listCentral.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Pull data from listSouth
shTemp.Range("G1").Select
For i = 0 To listSouth.ListCount - 1
If listSouth.Selected(i) Then ActiveCell.Value = listSouth.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Combine lists into one column
shTemp.Range("C1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

shTemp.Range("E1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

shTemp.Range("G1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

'Sort list alphabetically
shTemp.Activate
Range("A1").Select
Range("A1:A362").Sort Key1:=Range("A1"), Order1:=xlAscending

shTemp.Range("A1").Select
Range(Selection, Selection.End(xlDown)).Copy

Call Calculate
End Sub
--------------------


all it does is create a list from listboxes, then copies it.

and below is the code for the calculate procedure, again edited. it
doesn't do much but copy and paste certain ranges (that's how i set up
my workbook).

i can tell this isn't run because the previous procedure exits at
copying, and nothing else is populated (according to my calculate
procedure).


Code:
--------------------
Public Sub Calculate()
'Copy list to sheets

If chDep Then
shScale.Range("$B3").PasteSpecial
End If

If chHum Then
shScale.Range("$P3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shGrowth.Range("$B3").PasteSpecial
End If

If chHum Then
shGrowth.Range("$N3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shAccOpp.Range("$B3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shFormPos.Range("$B3").PasteSpecial
End If

If chHum Then
shFormPos.Range("$P3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shCap.Range("$B3").PasteSpecial
End If

If chHum Then
shCap.Range("$L3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shComp.Range("$B3").PasteSpecial
End If

If chHum Then
shComp.Range("$N3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shOpp.Range("$B3").PasteSpecial
End If

If chHum Then
shOpp.Range("$J3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shFit.Range("$B3").PasteSpecial
End If

If chHum Then
shFit.Range("$J3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shOppFit.Range("$B4").PasteSpecial
End If

If chHum Then
shOppFit.Range("$K4").PasteSpecial
End If

'Update bubble charts
If chDep Then
shOppFit.Activate
shOppFit.Range("C4:H4").Copy
shOppFit.Range("B4").End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 5)).PasteSpecial
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

If chHum Then
shOppFit.Activate
shOppFit.Range("L4:Q4").Copy
shOppFit.Range("K4").End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 5)).PasteSpecial
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

'Update single charts

'If chTr then
'If chOO then

If chDep Then
shOppFit.Range("H4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("K1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

shOppFit.Range("G4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("L1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

If chHum Then
shOppFit.Range("Q4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("N1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

shOppFit.Range("P4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("O1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

'Sort scores
shTemp.Activate
shTemp.Range("K1:L1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("L1"), Order1:=xlDescending

shTemp.Range("N1:O1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("O1"), Order1:=xlDescending

'Update other charts

If chDep Then
shCap.Activate
shCap.Range("B3:E3").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Activate
shTemp.Range("W1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp.Range("Y1").Value = "=VLOOKUP($W1,'MSA Abbr.'!$A$1:$B$417,2,FALSE)"
shTemp.Range("Y1").Select
ActiveCell.Copy
shTemp.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial
End If

If chHum Then
shCap.Activate
shCap.Range("L3:O3").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Activate
shTemp.Range("AB1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp.Range("AD1").Value = "=VLOOKUP($AB1,'MSA Abbr.'!$A$1:$B$417,2,FALSE)"
shTemp.Range("AD1").Select
ActiveCell.Copy
shTemp.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial
End If

'Update charts

If chDep Then
shOppFit.Activate
shOppFit.Range("B4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp2.Activate
shTemp2.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp2.Range("B1:F1").Copy
shTemp2.Range("A1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, ActiveCell.Offset(0, 4)).PasteSpecial
Selection.Copy
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

If chHum Then
shOppFit.Activate
shOppFit.Range("K4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp2.Activate
shTemp2.Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp2.Range("I1:N1").Copy
shTemp2.Range("H1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, ActiveCell.Offset(0, 5)).PasteSpecial
Selection.Copy
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If


Unload Me
Start.Activate
Application.ScreenUpdating = True

End Sub
--------------------


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Reply With QuoteReply With Quote