Help!!! Project returning "Out Memory"

    Hi all,


    My project has ballooned and it is now returning "Out of Memory" errors. I have deleted all unnecessary code and worksheets. Can anyone give me any suggestions of other things to try.


    My biggest memory hogs are the half dozen or more columns (each column with at least 240 rows) which run a macro using the find method when the user double-clicks a cell.


    Currently, I running this code:


    >>>>>>>>>>>>>>>>>>>>>
    IPrivate Sub Worksheet_BeforeDoubleClick( _
    ByVal target As Excel.Range, _
    Cancel As Boolean)
    Dim addr As String
    addr = target.Address
    If addr = "$A$1" Then
    End If
    If addr = "$A$2" Then
    End If
    If addr = "$A$3" Then
    End If
    and so on................
    End Sub
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    and it works spendidly. My problems is that my code includes a reference for each cell where I want this "Double-Click" event to occur, thus I have created a memory hog.


    Can this routine be altered to give the same result with less code?


    Can looping be incorporated with this code?

    Can any give an example of some code
    that acheives the desire goal by looping through these statements?


    Help Please!


    Chris:puzzled:

    each IF arguement will be checked by your code..... so even if the first is TRUE and executes the next to check....


    if they are all mutaully exclusive, ie only one IF will ever be TRUE, you may be better off using the SELECT CASE command, where when one is TRUE, none of the others are looked at :


    from Walkenbach :


    "when VBA executes a select case structure, the structure is exited as soon as VBA finds a true case"


    an example :


    Select case Quantity
    case 0 to 24 : mydiscount= 0.01
    case 25 to 49 : mydiscount = 0.02
    case 50 to 99 : mydiscount = 0.03
    case is >=100 : my discount = 0.04
    End select


    maybe you can slot your if statements into a similar structure to save memory where it's currently checking every condition, even if it's hit one that's already true... ?

    With Select you can speed it up by putting the more likely results first so that it exits as early a possible

    There are three types of people in this world.
    Those who can count and those who can't.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login