Consulting

Results 1 to 4 of 4

Thread: Excel Macro Procedure Too Large Error - Help

  1. #1

    Excel Macro Procedure Too Large Error - Help

    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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,645
    It might be helpful to start a VBA course for absolute beginners first.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •