Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with memory
I would like to macro to do the following:
Go to a worksheet, call it joe1. Go to cell E13, remember its contents, then replace them with zero. Repeat this for 13 more worksheets, joe2 thru joe14 (though they are not named sequentially, but assume joe1 thru joe 14 for discussion). Then go to cell F11 of the worksheet called jane, find its value, and edit-paste-special-value that result into cell H14 of a worksheet called Harry. Then return to those 14 worksheets and put their cell content back to the way it was before we put zero there, potentially it could be a formula, though I doubt it. I suppose I could do this myself by just copying cell E13 down, say, 1000 rows, replace it with zero, do my business as noted above, then, at the end, copy back up 1000 rows (then erase the copy, just to be rid of it at the end), but that seems sort of inelegant! Any more elegant ideas! Thanks! Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with memory
How is it inelegant if you know how to do it and can maintain and debug it.
Your alternative is to store things in arrays (untested pseudo code) Dim i as Long, sh as Worksheet Dim v as Variant Dim vE13(1 to 14) as Variant v = Array("Joe1","Joe2",. . .,"Joe14") for i = 1 to 14 set sh = worksheets(v(i)) vE13(i) = sh.Range("E13").Formula sh.Range("E13").Value = 0 Next i worksheets("Harry").Range("H14").value = Worksheets("Jane").Range("F11").value for i = 1 to 14 set sh = worksheets(v(i)) sh.Range("E13").Formula = vE13(i) Next i Note that if a cell contains a constant rather than a formula, using the formula property will still work. -- Regards, Tom Ogilvy "Dean" wrote in message ... I would like to macro to do the following: Go to a worksheet, call it joe1. Go to cell E13, remember its contents, then replace them with zero. Repeat this for 13 more worksheets, joe2 thru joe14 (though they are not named sequentially, but assume joe1 thru joe 14 for discussion). Then go to cell F11 of the worksheet called jane, find its value, and edit-paste-special-value that result into cell H14 of a worksheet called Harry. Then return to those 14 worksheets and put their cell content back to the way it was before we put zero there, potentially it could be a formula, though I doubt it. I suppose I could do this myself by just copying cell E13 down, say, 1000 rows, replace it with zero, do my business as noted above, then, at the end, copy back up 1000 rows (then erase the copy, just to be rid of it at the end), but that seems sort of inelegant! Any more elegant ideas! Thanks! Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Hogging Memory - what to do? | Excel Programming | |||
Crashing Macro, Not Enough Memory | Excel Programming | |||
Error 7 Out of Memory 2/3 through Macro | Excel Programming | |||
XP running out of memory when using macro | Excel Programming | |||
Not enough Memory running macro | Excel Programming |