Macro with memory

[Dean]

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

 

[Tom Ogilvy]

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.