PDA

View Full Version : Excel Macro Procedure Too Large Error - Help



sanjaydutta2
05-29-2015, 02:19 AM
Hi,

I am getting Procedure too Large Error when i am trying to use this macro. Kindly give a solution or smaller version.



Private Sub Worksheet_Calculate()



Static old_value9 As Variant: Static old_value10 As Variant: Static old_value11 As Variant: Static old_value12 As Variant: Static old_value13 As Variant: Static old_value14 As Variant
Static old_value15 As Variant
Static old_value16 As Variant
Static old_value17 As Variant
Static old_value18 As Variant
Static old_value19 As Variant
Static old_value20 As Variant
Static old_value21 As Variant
Static old_value22 As Variant
Static old_value23 As Variant
Static old_value24 As Variant
Static old_value25 As Variant
Static old_value26 As Variant
Static old_value27 As Variant
Static old_value28 As Variant
Static old_value29 As Variant
Static old_value30 As Variant
Static old_value31 As Variant
Static old_value32 As Variant
Static old_value33 As Variant
Static old_value34 As Variant
Static old_value35 As Variant
Static old_value36 As Variant
Static old_value37 As Variant
Static old_value38 As Variant
Static old_value39 As Variant
Static old_value40 As Variant
Static old_value41 As Variant
Static old_value42 As Variant
Static old_value43 As Variant
Static old_value44 As Variant
Static old_value45 As Variant
Static old_value46 As Variant
Static old_value47 As Variant
Static old_value48 As Variant
Static old_value49 As Variant
Static old_value50 As Variant
Static old_value51 As Variant
Static old_value52 As Variant
Static old_value53 As Variant
Static old_value54 As Variant
Static old_value55 As Variant
Static old_value56 As Variant
Static old_value57 As Variant
Static old_value58 As Variant
Static old_value59 As Variant
Static old_value60 As Variant
Static old_value61 As Variant
Static old_value62 As Variant
Static old_value63 As Variant
Static old_value64 As Variant
Static old_value65 As Variant
Static old_value66 As Variant
Static old_value67 As Variant
Static old_value68 As Variant
Static old_value69 As Variant
Static old_value70 As Variant
Static old_value71 As Variant
Static old_value72 As Variant
Static old_value73 As Variant
Static old_value74 As Variant
Static old_value75 As Variant
Static old_value76 As Variant
Static old_value77 As Variant
Static old_value78 As Variant
Static old_value79 As Variant
Static old_value80 As Variant
Static old_value81 As Variant
Static old_value82 As Variant
Static old_value83 As Variant
Static old_value84 As Variant
Static old_value85 As Variant
Static old_value86 As Variant
Static old_value87 As Variant
Static old_value88 As Variant
Static old_value89 As Variant
Static old_value90 As Variant
Static old_value91 As Variant
Static old_value92 As Variant
Static old_value93 As Variant
Static old_value94 As Variant
Static old_value95 As Variant
Static old_value96 As Variant
Static old_value97 As Variant
Static old_value98 As Variant
Static old_value99 As Variant
Static old_value100 As Variant
Static old_value101 As Variant
Static old_value102 As Variant
Static old_value103 As Variant
Static old_value104 As Variant
Static old_value105 As Variant
Static old_value106 As Variant
Static old_value107 As Variant
Static old_value108 As Variant
Static old_value109 As Variant
Static old_value110 As Variant
Static old_value111 As Variant
Static old_value112 As Variant
Static old_value113 As Variant
Static old_value114 As Variant
Static old_value115 As Variant
Static old_value116 As Variant
Static old_value117 As Variant
Static old_value118 As Variant
Static old_value119 As Variant
Static old_value120 As Variant
Static old_value121 As Variant
Static old_value122 As Variant
Static old_value123 As Variant
Static old_value124 As Variant
Static old_value125 As Variant
Static old_value126 As Variant
Static old_value127 As Variant
Static old_value128 As Variant
Static old_value129 As Variant
Static old_value130 As Variant
Static old_value131 As Variant
Static old_value132 As Variant



Static old_LTP9 As Variant
Static old_LTP10 As Variant
Static old_LTP11 As Variant
Static old_LTP12 As Variant
Static old_LTP13 As Variant
Static old_LTP14 As Variant
Static old_LTP15 As Variant
Static old_LTP16 As Variant
Static old_LTP17 As Variant
Static old_LTP18 As Variant
Static old_LTP19 As Variant
Static old_LTP20 As Variant
Static old_LTP21 As Variant
Static old_LTP22 As Variant
Static old_LTP23 As Variant
Static old_LTP24 As Variant
Static old_LTP25 As Variant
Static old_LTP26 As Variant
Static old_LTP27 As Variant
Static old_LTP28 As Variant
Static old_LTP29 As Variant
Static old_LTP30 As Variant
Static old_LTP31 As Variant
Static old_LTP32 As Variant
Static old_LTP33 As Variant
Static old_LTP34 As Variant
Static old_LTP35 As Variant
Static old_LTP36 As Variant
Static old_LTP37 As Variant
Static old_LTP38 As Variant
Static old_LTP39 As Variant
Static old_LTP40 As Variant
Static old_LTP41 As Variant
Static old_LTP42 As Variant
Static old_LTP43 As Variant
Static old_LTP44 As Variant
Static old_LTP45 As Variant
Static old_LTP46 As Variant
Static old_LTP47 As Variant
Static old_LTP48 As Variant
Static old_LTP49 As Variant
Static old_LTP50 As Variant
Static old_LTP51 As Variant
Static old_LTP52 As Variant
Static old_LTP53 As Variant
Static old_LTP54 As Variant
Static old_LTP55 As Variant
Static old_LTP56 As Variant
Static old_LTP57 As Variant
Static old_LTP58 As Variant
Static old_LTP59 As Variant
Static old_LTP60 As Variant
Static old_LTP61 As Variant
Static old_LTP62 As Variant
Static old_LTP63 As Variant
Static old_LTP64 As Variant
Static old_LTP65 As Variant
Static old_LTP66 As Variant
Static old_LTP67 As Variant
Static old_LTP68 As Variant
Static old_LTP69 As Variant
Static old_LTP70 As Variant
Static old_LTP71 As Variant
Static old_LTP72 As Variant
Static old_LTP73 As Variant
Static old_LTP74 As Variant
Static old_LTP75 As Variant
Static old_LTP76 As Variant
Static old_LTP77 As Variant
Static old_LTP78 As Variant
Static old_LTP79 As Variant
Static old_LTP80 As Variant
Static old_LTP81 As Variant
Static old_LTP82 As Variant
Static old_LTP83 As Variant
Static old_LTP84 As Variant
Static old_LTP85 As Variant
Static old_LTP86 As Variant
Static old_LTP87 As Variant
Static old_LTP88 As Variant
Static old_LTP89 As Variant
Static old_LTP90 As Variant
Static old_LTP91 As Variant
Static old_LTP92 As Variant
Static old_LTP93 As Variant
Static old_LTP94 As Variant
Static old_LTP95 As Variant
Static old_LTP96 As Variant
Static old_LTP97 As Variant
Static old_LTP98 As Variant
Static old_LTP99 As Variant
Static old_LTP100 As Variant
Static old_LTP101 As Variant
Static old_LTP102 As Variant
Static old_LTP103 As Variant
Static old_LTP104 As Variant
Static old_LTP105 As Variant
Static old_LTP106 As Variant
Static old_LTP107 As Variant
Static old_LTP108 As Variant
Static old_LTP109 As Variant
Static old_LTP110 As Variant
Static old_LTP111 As Variant
Static old_LTP112 As Variant
Static old_LTP113 As Variant
Static old_LTP114 As Variant
Static old_LTP115 As Variant
Static old_LTP116 As Variant
Static old_LTP117 As Variant
Static old_LTP118 As Variant
Static old_LTP119 As Variant
Static old_LTP120 As Variant
Static old_LTP121 As Variant
Static old_LTP122 As Variant
Static old_LTP123 As Variant
Static old_LTP124 As Variant
Static old_LTP125 As Variant
Static old_LTP126 As Variant
Static old_LTP127 As Variant
Static old_LTP128 As Variant
Static old_LTP129 As Variant
Static old_LTP130 As Variant
Static old_LTP131 As Variant
Static old_LTP132 As Variant

Static old_Vol9 As Variant
Static old_Vol10 As Variant
Static old_Vol11 As Variant
Static old_Vol12 As Variant
Static old_Vol13 As Variant
Static old_Vol14 As Variant
Static old_Vol15 As Variant
Static old_Vol16 As Variant
Static old_Vol17 As Variant
Static old_Vol18 As Variant
Static old_Vol19 As Variant
Static old_Vol20 As Variant
Static old_Vol21 As Variant
Static old_Vol22 As Variant
Static old_Vol23 As Variant
Static old_Vol24 As Variant
Static old_Vol25 As Variant
Static old_Vol26 As Variant
Static old_Vol27 As Variant
Static old_Vol28 As Variant
Static old_Vol29 As Variant
Static old_Vol30 As Variant
Static old_Vol31 As Variant
Static old_Vol32 As Variant
Static old_Vol33 As Variant
Static old_Vol34 As Variant
Static old_Vol35 As Variant
Static old_Vol36 As Variant
Static old_Vol37 As Variant
Static old_Vol38 As Variant
Static old_Vol39 As Variant
Static old_Vol40 As Variant
Static old_Vol41 As Variant
Static old_Vol42 As Variant
Static old_Vol43 As Variant
Static old_Vol44 As Variant
Static old_Vol45 As Variant
Static old_Vol46 As Variant
Static old_Vol47 As Variant
Static old_Vol48 As Variant
Static old_Vol49 As Variant
Static old_Vol50 As Variant
Static old_Vol51 As Variant
Static old_Vol52 As Variant
Static old_Vol53 As Variant
Static old_Vol54 As Variant
Static old_Vol55 As Variant
Static old_Vol56 As Variant
Static old_Vol57 As Variant
Static old_Vol58 As Variant
Static old_Vol59 As Variant
Static old_Vol60 As Variant
Static old_Vol61 As Variant
Static old_Vol62 As Variant
Static old_Vol63 As Variant
Static old_Vol64 As Variant
Static old_Vol65 As Variant
Static old_Vol66 As Variant
Static old_Vol67 As Variant
Static old_Vol68 As Variant
Static old_Vol69 As Variant
Static old_Vol70 As Variant
Static old_Vol71 As Variant
Static old_Vol72 As Variant
Static old_Vol73 As Variant
Static old_Vol74 As Variant
Static old_Vol75 As Variant
Static old_Vol76 As Variant
Static old_Vol77 As Variant
Static old_Vol78 As Variant
Static old_Vol79 As Variant
Static old_Vol80 As Variant
Static old_Vol81 As Variant
Static old_Vol82 As Variant
Static old_Vol83 As Variant
Static old_Vol84 As Variant
Static old_Vol85 As Variant
Static old_Vol86 As Variant
Static old_Vol87 As Variant
Static old_Vol88 As Variant
Static old_Vol89 As Variant
Static old_Vol90 As Variant
Static old_Vol91 As Variant
Static old_Vol92 As Variant
Static old_Vol93 As Variant
Static old_Vol94 As Variant
Static old_Vol95 As Variant
Static old_Vol96 As Variant
Static old_Vol97 As Variant
Static old_Vol98 As Variant
Static old_Vol99 As Variant
Static old_Vol100 As Variant
Static old_Vol101 As Variant
Static old_Vol102 As Variant
Static old_Vol103 As Variant
Static old_Vol104 As Variant
Static old_Vol105 As Variant
Static old_Vol106 As Variant
Static old_Vol107 As Variant
Static old_Vol108 As Variant
Static old_Vol109 As Variant
Static old_Vol110 As Variant
Static old_Vol111 As Variant
Static old_Vol112 As Variant
Static old_Vol113 As Variant
Static old_Vol114 As Variant
Static old_Vol115 As Variant
Static old_Vol116 As Variant
Static old_Vol117 As Variant
Static old_Vol118 As Variant
Static old_Vol119 As Variant
Static old_Vol120 As Variant
Static old_Vol121 As Variant
Static old_Vol122 As Variant
Static old_Vol123 As Variant
Static old_Vol124 As Variant
Static old_Vol125 As Variant
Static old_Vol126 As Variant
Static old_Vol127 As Variant
Static old_Vol128 As Variant
Static old_Vol129 As Variant
Static old_Vol130 As Variant
Static old_Vol131 As Variant
Static old_Vol132 As Variant
Static OLD_TIME As Variant


Select Case Range("I9").Value
Case Is <> OLD_TIME
Range("T1").Value = Range("T1").Value + (Range("I9").Value - OLD_TIME): OLD_TIME = Range("I9").Value
End Select


Select Case Range("G9").Value
Case Is < old_value9
Range("T9").Value = Range("T9").Value + (Range("D9").Value - old_Vol9): old_Vol9 = Range("D9").Value: old_value9 = Range("G9").Value: Range("Z9").Value = Range("Z9").Value + 1
Case Is > old_value9
Range("U9").Value = Range("U9").Value + (Range("D9").Value - old_Vol9): old_Vol9 = Range("D9").Value: old_value9 = Range("G9").Value: Range("Z9").Value = Range("Z9").Value + 1
End Select

Select Case Range("G10").Value
Case Is < old_value10
Range("T10").Value = Range("T10").Value + (Range("D10").Value - old_Vol10): old_Vol10 = Range("D10").Value: old_value10 = Range("G10").Value: Range("Z10").Value = Range("Z10").Value + 1
Case Is > old_value10
Range("U10").Value = Range("U10").Value + (Range("D10").Value - old_Vol10): old_Vol10 = Range("D10").Value: old_value10 = Range("G10").Value: Range("Z10").Value = Range("Z10").Value + 1
End Select

Select Case Range("G11").Value
Case Is < old_value11
Range("T11").Value = Range("T11").Value + (Range("D11").Value - old_Vol11): old_Vol11 = Range("D11").Value: old_value11 = Range("G11").Value: Range("Z11").Value = Range("Z11").Value + 1
Case Is > old_value11
Range("U11").Value = Range("U11").Value + (Range("D11").Value - old_Vol11): old_Vol11 = Range("D11").Value: old_value11 = Range("G11").Value: Range("Z11").Value = Range("Z11").Value + 1
End Select

Select Case Range("G12").Value
Case Is < old_value12
Range("T12").Value = Range("T12").Value + (Range("D12").Value - old_Vol12): old_Vol12 = Range("D12").Value: old_value12 = Range("G12").Value: Range("Z12").Value = Range("Z12").Value + 1
Case Is > old_value12
Range("U12").Value = Range("U12").Value + (Range("D12").Value - old_Vol12): old_Vol12 = Range("D12").Value: old_value12 = Range("G12").Value: Range("Z12").Value = Range("Z12").Value + 1
End Select

Select Case Range("G13").Value
Case Is < old_value13
Range("T13").Value = Range("T13").Value + (Range("D13").Value - old_Vol13): old_Vol13 = Range("D13").Value: old_value13 = Range("G13").Value: Range("Z13").Value = Range("Z13").Value + 1
Case Is > old_value13
Range("U13").Value = Range("U13").Value + (Range("D13").Value - old_Vol13): old_Vol13 = Range("D13").Value: old_value13 = Range("G13").Value: Range("Z13").Value = Range("Z13").Value + 1
End Select

Select Case Range("G14").Value
Case Is < old_value14
Range("T14").Value = Range("T14").Value + (Range("D14").Value - old_Vol14): old_Vol14 = Range("D14").Value: old_value14 = Range("G14").Value: Range("Z14").Value = Range("Z14").Value + 1
Case Is > old_value14
Range("U14").Value = Range("U14").Value + (Range("D14").Value - old_Vol14): old_Vol14 = Range("D14").Value: old_value14 = Range("G14").Value: Range("Z14").Value = Range("Z14").Value + 1
End Select

Select Case Range("G15").Value
Case Is < old_value15
Range("T15").Value = Range("T15").Value + (Range("D15").Value - old_Vol15): old_Vol15 = Range("D15").Value: old_value15 = Range("G15").Value: Range("Z15").Value = Range("Z15").Value + 1
Case Is > old_value15
Range("U15").Value = Range("U15").Value + (Range("D15").Value - old_Vol15): old_Vol15 = Range("D15").Value: old_value15 = Range("G15").Value: Range("Z15").Value = Range("Z15").Value + 1
End Select

Select Case Range("G16").Value
Case Is < old_value16
Range("T16").Value = Range("T16").Value + (Range("D16").Value - old_Vol16): old_Vol16 = Range("D16").Value: old_value16 = Range("G16").Value: Range("Z16").Value = Range("Z16").Value + 1
Case Is > old_value16
Range("U16").Value = Range("U16").Value + (Range("D16").Value - old_Vol16): old_Vol16 = Range("D16").Value: old_value16 = Range("G16").Value: Range("Z16").Value = Range("Z16").Value + 1
End Select

Select Case Range("G17").Value
Case Is < old_value17
Range("T17").Value = Range("T17").Value + (Range("D17").Value - old_Vol17): old_Vol17 = Range("D17").Value: old_value17 = Range("G17").Value: Range("Z17").Value = Range("Z17").Value + 1
Case Is > old_value17
Range("U17").Value = Range("U17").Value + (Range("D17").Value - old_Vol17): old_Vol17 = Range("D17").Value: old_value17 = Range("G17").Value: Range("Z17").Value = Range("Z17").Value + 1
End Select

Select Case Range("G18").Value
Case Is < old_value18
Range("T18").Value = Range("T18").Value + (Range("D18").Value - old_Vol18): old_Vol18 = Range("D18").Value: old_value18 = Range("G18").Value: Range("Z18").Value = Range("Z18").Value + 1
Case Is > old_value18
Range("U18").Value = Range("U18").Value + (Range("D18").Value - old_Vol18): old_Vol18 = Range("D18").Value: old_value18 = Range("G18").Value: Range("Z18").Value = Range("Z18").Value + 1
End Select

Select Case Range("G19").Value
Case Is < old_value19
Range("T19").Value = Range("T19").Value + (Range("D19").Value - old_Vol19): old_Vol19 = Range("D19").Value: old_value19 = Range("G19").Value: Range("Z19").Value = Range("Z19").Value + 1
Case Is > old_value19
Range("U19").Value = Range("U19").Value + (Range("D19").Value - old_Vol19): old_Vol19 = Range("D19").Value: old_value19 = Range("G19").Value: Range("Z19").Value = Range("Z19").Value + 1
End Select

Select Case Range("G20").Value
Case Is < old_value20
Range("T20").Value = Range("T20").Value + (Range("D20").Value - old_Vol20): old_Vol20 = Range("D20").Value: old_value20 = Range("G20").Value: Range("Z20").Value = Range("Z20").Value + 1
Case Is > old_value20
Range("U20").Value = Range("U20").Value + (Range("D20").Value - old_Vol20): old_Vol20 = Range("D20").Value: old_value20 = Range("G20").Value: Range("Z20").Value = Range("Z20").Value + 1
End Select

Select Case Range("G21").Value
Case Is < old_value21
Range("T21").Value = Range("T21").Value + (Range("D21").Value - old_Vol21): old_Vol21 = Range("D21").Value: old_value21 = Range("G21").Value: Range("Z21").Value = Range("Z21").Value + 1
Case Is > old_value21
Range("U21").Value = Range("U21").Value + (Range("D21").Value - old_Vol21): old_Vol21 = Range("D21").Value: old_value21 = Range("G21").Value: Range("Z21").Value = Range("Z21").Value + 1
End Select

Select Case Range("G22").Value
Case Is < old_value22
Range("T22").Value = Range("T22").Value + (Range("D22").Value - old_Vol22): old_Vol22 = Range("D22").Value: old_value22 = Range("G22").Value: Range("Z22").Value = Range("Z22").Value + 1
Case Is > old_value22
Range("U22").Value = Range("U22").Value + (Range("D22").Value - old_Vol22): old_Vol22 = Range("D22").Value: old_value22 = Range("G22").Value: Range("Z22").Value = Range("Z22").Value + 1
End Select

Select Case Range("G23").Value
Case Is < old_value23
Range("T23").Value = Range("T23").Value + (Range("D23").Value - old_Vol23): old_Vol23 = Range("D23").Value: old_value23 = Range("G23").Value: Range("Z23").Value = Range("Z23").Value + 1
Case Is > old_value23
Range("U23").Value = Range("U23").Value + (Range("D23").Value - old_Vol23): old_Vol23 = Range("D23").Value: old_value23 = Range("G23").Value: Range("Z23").Value = Range("Z23").Value + 1
End Select

Select Case Range("G24").Value
Case Is < old_value24
Range("T24").Value = Range("T24").Value + (Range("D24").Value - old_Vol24): old_Vol24 = Range("D24").Value: old_value24 = Range("G24").Value: Range("Z24").Value = Range("Z24").Value + 1
Case Is > old_value24
Range("U24").Value = Range("U24").Value + (Range("D24").Value - old_Vol24): old_Vol24 = Range("D24").Value: old_value24 = Range("G24").Value: Range("Z24").Value = Range("Z24").Value + 1
End Select

Select Case Range("G25").Value
Case Is < old_value25
Range("T25").Value = Range("T25").Value + (Range("D25").Value - old_Vol25): old_Vol25 = Range("D25").Value: old_value25 = Range("G25").Value: Range("Z25").Value = Range("Z25").Value + 1
Case Is > old_value25
Range("U25").Value = Range("U25").Value + (Range("D25").Value - old_Vol25): old_Vol25 = Range("D25").Value: old_value25 = Range("G25").Value: Range("Z25").Value = Range("Z25").Value + 1
End Select

Select Case Range("G26").Value
Case Is < old_value26
Range("T26").Value = Range("T26").Value + (Range("D26").Value - old_Vol26): old_Vol26 = Range("D26").Value: old_value26 = Range("G26").Value: Range("Z26").Value = Range("Z26").Value + 1
Case Is > old_value26
Range("U26").Value = Range("U26").Value + (Range("D26").Value - old_Vol26): old_Vol26 = Range("D26").Value: old_value26 = Range("G26").Value: Range("Z26").Value = Range("Z26").Value + 1
End Select

Select Case Range("G27").Value
Case Is < old_value27
Range("T27").Value = Range("T27").Value + (Range("D27").Value - old_Vol27): old_Vol27 = Range("D27").Value: old_value27 = Range("G27").Value: Range("Z27").Value = Range("Z27").Value + 1
Case Is > old_value27
Range("U27").Value = Range("U27").Value + (Range("D27").Value - old_Vol27): old_Vol27 = Range("D27").Value: old_value27 = Range("G27").Value: Range("Z27").Value = Range("Z27").Value + 1
End Select

Select Case Range("G28").Value
Case Is < old_value28
Range("T28").Value = Range("T28").Value + (Range("D28").Value - old_Vol28): old_Vol28 = Range("D28").Value: old_value28 = Range("G28").Value: Range("Z28").Value = Range("Z28").Value + 1
Case Is > old_value28
Range("U28").Value = Range("U28").Value + (Range("D28").Value - old_Vol28): old_Vol28 = Range("D28").Value: old_value28 = Range("G28").Value: Range("Z28").Value = Range("Z28").Value + 1
End Select

Select Case Range("G29").Value
Case Is < old_value29
Range("T29").Value = Range("T29").Value + (Range("D29").Value - old_Vol29): old_Vol29 = Range("D29").Value: old_value29 = Range("G29").Value: Range("Z29").Value = Range("Z29").Value + 1
Case Is > old_value29
Range("U29").Value = Range("U29").Value + (Range("D29").Value - old_Vol29): old_Vol29 = Range("D29").Value: old_value29 = Range("G29").Value: Range("Z29").Value = Range("Z29").Value + 1
End Select

Select Case Range("G30").Value
Case Is < old_value30
Range("T30").Value = Range("T30").Value + (Range("D30").Value - old_Vol30): old_Vol30 = Range("D30").Value: old_value30 = Range("G30").Value: Range("Z30").Value = Range("Z30").Value + 1
Case Is > old_value30
Range("U30").Value = Range("U30").Value + (Range("D30").Value - old_Vol30): old_Vol30 = Range("D30").Value: old_value30 = Range("G30").Value: Range("Z30").Value = Range("Z30").Value + 1
End Select

Select Case Range("G31").Value
Case Is < old_value31
Range("T31").Value = Range("T31").Value + (Range("D31").Value - old_Vol31): old_Vol31 = Range("D31").Value: old_value31 = Range("G31").Value: Range("Z31").Value = Range("Z31").Value + 1
Case Is > old_value31
Range("U31").Value = Range("U31").Value + (Range("D31").Value - old_Vol31): old_Vol31 = Range("D31").Value: old_value31 = Range("G31").Value: Range("Z31").Value = Range("Z31").Value + 1
End Select

Select Case Range("G32").Value
Case Is < old_value32
Range("T32").Value = Range("T32").Value + (Range("D32").Value - old_Vol32): old_Vol32 = Range("D32").Value: old_value32 = Range("G32").Value: Range("Z32").Value = Range("Z32").Value + 1
Case Is > old_value32
Range("U32").Value = Range("U32").Value + (Range("D32").Value - old_Vol32): old_Vol32 = Range("D32").Value: old_value32 = Range("G32").Value: Range("Z32").Value = Range("Z32").Value + 1
End Select

Select Case Range("G33").Value
Case Is < old_value33
Range("T33").Value = Range("T33").Value + (Range("D33").Value - old_Vol33): old_Vol33 = Range("D33").Value: old_value33 = Range("G33").Value: Range("Z33").Value = Range("Z33").Value + 1
Case Is > old_value33
Range("U33").Value = Range("U33").Value + (Range("D33").Value - old_Vol33): old_Vol33 = Range("D33").Value: old_value33 = Range("G33").Value: Range("Z33").Value = Range("Z33").Value + 1
End Select

Select Case Range("G34").Value
Case Is < old_value34
Range("T34").Value = Range("T34").Value + (Range("D34").Value - old_Vol34): old_Vol34 = Range("D34").Value: old_value34 = Range("G34").Value: Range("Z34").Value = Range("Z34").Value + 1
Case Is > old_value34
Range("U34").Value = Range("U34").Value + (Range("D34").Value - old_Vol34): old_Vol34 = Range("D34").Value: old_value34 = Range("G34").Value: Range("Z34").Value = Range("Z34").Value + 1
End Select

Select Case Range("G35").Value
Case Is < old_value35
Range("T35").Value = Range("T35").Value + (Range("D35").Value - old_Vol35): old_Vol35 = Range("D35").Value: old_value35 = Range("G35").Value: Range("Z35").Value = Range("Z35").Value + 1
Case Is > old_value35
Range("U35").Value = Range("U35").Value + (Range("D35").Value - old_Vol35): old_Vol35 = Range("D35").Value: old_value35 = Range("G35").Value: Range("Z35").Value = Range("Z35").Value + 1
End Select

Select Case Range("G36").Value
Case Is < old_value36
Range("T36").Value = Range("T36").Value + (Range("D36").Value - old_Vol36): old_Vol36 = Range("D36").Value: old_value36 = Range("G36").Value: Range("Z36").Value = Range("Z36").Value + 1
Case Is > old_value36
Range("U36").Value = Range("U36").Value + (Range("D36").Value - old_Vol36): old_Vol36 = Range("D36").Value: old_value36 = Range("G36").Value: Range("Z36").Value = Range("Z36").Value + 1
End Select

Select Case Range("G37").Value
Case Is < old_value37
Range("T37").Value = Range("T37").Value + (Range("D37").Value - old_Vol37): old_Vol37 = Range("D37").Value: old_value37 = Range("G37").Value: Range("Z37").Value = Range("Z37").Value + 1
Case Is > old_value37
Range("U37").Value = Range("U37").Value + (Range("D37").Value - old_Vol37): old_Vol37 = Range("D37").Value: old_value37 = Range("G37").Value: Range("Z37").Value = Range("Z37").Value + 1
End Select

Select Case Range("G38").Value
Case Is < old_value38
Range("T38").Value = Range("T38").Value + (Range("D38").Value - old_Vol38): old_Vol38 = Range("D38").Value: old_value38 = Range("G38").Value: Range("Z38").Value = Range("Z38").Value + 1
Case Is > old_value38
Range("U38").Value = Range("U38").Value + (Range("D38").Value - old_Vol38): old_Vol38 = Range("D38").Value: old_value38 = Range("G38").Value: Range("Z38").Value = Range("Z38").Value + 1
End Select

Select Case Range("G39").Value
Case Is < old_value39
Range("T39").Value = Range("T39").Value + (Range("D39").Value - old_Vol39): old_Vol39 = Range("D39").Value: old_value39 = Range("G39").Value: Range("Z39").Value = Range("Z39").Value + 1
Case Is > old_value39
Range("U39").Value = Range("U39").Value + (Range("D39").Value - old_Vol39): old_Vol39 = Range("D39").Value: old_value39 = Range("G39").Value: Range("Z39").Value = Range("Z39").Value + 1
End Select

Select Case Range("G40").Value
Case Is < old_value40
Range("T40").Value = Range("T40").Value + (Range("D40").Value - old_Vol40): old_Vol40 = Range("D40").Value: old_value40 = Range("G40").Value: Range("Z40").Value = Range("Z40").Value + 1
Case Is > old_value40
Range("U40").Value = Range("U40").Value + (Range("D40").Value - old_Vol40): old_Vol40 = Range("D40").Value: old_value40 = Range("G40").Value: Range("Z40").Value = Range("Z40").Value + 1
End Select


' this goes upto range 150, but i have pasted less due to limitation of charactors on this forum

End Sub

snb
05-29-2015, 04:06 AM
It might be helpful to start a VBA course for absolute beginners first.

SamT
05-29-2015, 09:12 AM
I have never before see anyone run into the code module character limit beforeWhat snb said, but nicer. Fortunately, you have came to the right place. Welcome to VBA Express, the best place on the Webto learn VBA.


Still the wrong way to do what you're trying to do but replace

Static old_value9 As Variant
Static old_value10 As Variant
With

Static G9
Static G10
The default Variable Type is Variant.
You have a fantastic mind if you can keep all those straight.


Use a "Helper" worksheet. Copy the old sheet's cells onto a new sheet. Reference that instead of a bunch of static variables


Forget all that code. What are you trying to accomplish. Maybe we already know a better way.

Paul_Hossler
05-29-2015, 11:47 AM
Not sure about the 'Static' or why all that is in the WS Calculate


Suggestions:

Use a 'scratch' sheet to hold values

or

Arrays and .Cells(row,col) and loops would simplify things






Option Explicit
Private Sub Worksheet_Calculate()
Dim old_value(9 To 150) As Variant
Dim old_LTP(9 To 150) As Variant
Dim old_Vol(9 To 150) As Variant
Dim OLD_TIME As Variant

Dim i As Long

With ActiveSheet

Select Case .Cells(9, 9).Value
Case Is <> OLD_TIME
.Cells(1, 20).Value = .Cells(1, 20).Value + .Cells(1, 9).Value - OLD_TIME
OLD_TIME = .Cells(1, 9).Value
End Select

For i = 9 To 150
Select Case .Cells(i, 7).Value ' Range("G9")
Case Is < old_value(i)
.Cells(i, 20).Value = .Cells(i, 20).Value + .Cells(i, 4).Value - old_Vol(i)
old_Vol(i) = .Cells(i, 4).Value
old_value(i) = .Cells(i, 7).Value
.Cells(i, 26).Value = .Cells(i, 26).Value + 1
Case Is > old_value(i)
.Cells(i, 21).Value = .Cells(i, 21).Value + .Cells(i, 4).Value - old_Vol(i)
old_Vol(i) = .Cells(i, 4).Value
old_value(i) = .Cells(i, 7).Value
.Cells(i, 26).Value = .Cells(i, 26).Value + 1
End Select
Next i

End With
End Sub