Macro Loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to help someone with this looping this macro. Here's his code:

ActiveCell.Range("A1:G1").Select
Selection.Copy
ActiveCell.Offset(-30, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(24, -3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

I'm a rather inexperienced programmer, but my thoughts were to wrap this
with a For...Next which I think I could figure out. But, I also think that
the macro will have to begin in a certain cell each time in order for his
offsets to work.

So, any help would be appreciated that starts this macro at E36 and loops it
like 5000 times.

Thanks very much.
 
Patty, here is what the code currently does:

1. The ActiveCell object sets up a relative reference, which means that
wherever the cursor happens to be (which cell is active) is considered
Range("a1"). So if the cursor is on cell G15 the selected cells in this case
will be G15 through M15. Since his next command moves the cursor up 30 rows,
I assume that it had been placed lower on the worksheet by some code not
posted in this snippet.

2. If the intent is to copy the absolute cells $A$1:$G$1 and the cursor is
set anywhere above row 31 then the line ActiveCell.Offset(-30,0) will fail
because there will not be enough rows to accomplish the command.

3. Using the Range("A1") designation after ActiveCell.Offset( ) is not
necessary, the select will move the cursor and it will know where it is.

4. Disabling the CutCopyMode between copies is also unnecessary, each copy
command overrides the previous one so the disable command can be moved to the
end of all the copy processes.

5. I cleaned the code up a little as you can see below, but since you do
not provide information about the sheet layout and what criteria is being
used to select and copy the data, I am afraid I cannot help with the
For...Each...Next or even a Do...Loop.
 
Odd code. What is it supposed to do?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Here's his code in cell E36.

EXP( (LN(D36)*EXP((-$B$41))) + (LN($B$39)*(1-EXP(-$B$41))) -
((1-EXP(-2*$B$41))*($B$40^2)/(4*$B$41)) +
$B$40*SQRT((1-EXP(-2*$B$41))/(2*$B$41) )*E35)

When I'm in cell E36 and run his macro, it calculates a Net Present Value
and posts the value in B30 as well as in column A in Sheet 2. He wants it to
run many times over and over so he has a list of values in Sheet 2.

Sorry, I don't understand much more than that; would the worksheet help
and/or can I send attachments.

Thanks much again.
 
Not sure I understand you correct but start with this
When I'm in cell E36 and run his macro, it calculates a Net Present Value
Click to expand...
How do you calculate ?

With the formula in "Sheet1" this example loop 10 times and copy to column A in "Sheet2"

Sub test()
For I = 1 To 10

'calculate code, not sure what you do here ?

'now copy the value of the formula to Sheet2 in A&I
'Or do you want to make a list in B30:B? also in Sheet1
Sheets("Sheet2").Range("A" & I).Value = Sheets("Sheet1").Range("E36").Value
Next I

End Sub
 
Back
Top