Loop problem 2

[vba317]

In the following loop the loop works but it has a logic error that I can't fix. It does the calculations I want but for too many rows not the rows I define. As an example: I am summing up 600+ rows. I want the formula to start at row 20 instead it starts at row 4. I don't wat to redefine the for part of the loop because I have other formulas that I have in this loop. Any help would be appreciated.

Dim R2 as Double
Dim LR as Double

LR = ActiveSheet.UsedRange.Rows.Count

For R2 = 4 To LR - 1

If R2 > 20 Then Range("U20" & ":" & "U" & R2).Formula = "=SUM(" & Range(Cells(ADH20, "E"), Cells(ADL20, "E")).Address(False, False) & ")/20"

Next R2

 

[SkipVought]

Hi,

Why are you coding a spreadsheet formula? Why not just do it all on your sheet?

If you must code, what is the value assigned to these two variables...

ADH20 & ADL20


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

ADL20 = 1
ADH20 = 20

 

[SkipVought]

so your formula is
[tt]
=SUM(E1:E20)/20
[/tt]
Again, Why not just do it all on your sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I reread your question. The reason why I am coding this is because I am doing the same action to 20 spreadsheets every day. If I use your suggestion how is that going to stop the code from calculating on cells below 20?

 

[SkipVought]

stop the code from calculating on cells below 20?

[red]
Change the operator[/red]

     [b][red]
      |
      V[/red][/b]
If R2 [b][red]> [/red][/b]20 Then Range("U20" & ":" & "U" & R2).Formula = "=SUM(" & Range(Cells(ADH20, "E"), Cells(ADL20, "E")).Address(False, False) & ")/20"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Change the operator to what?

 

[PHV]

Why not simply replace this:
For R2 = 4 To LR - 1
with this ?
For R2 = 20 To LR - 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Change the reference style to absolute and do it without looping:

FormulaString = "=SUM(" & Range(Cells(ADH20, "E"), Cells(ADL20, "E")).Address(True, True) & ")/20"
Range("U20" & ":" & "U" & LR-1).Formula = FormulaString

combo

 

[vba317]

This is a response to pub. I thought of doing creating a loop like you described. If I approach the loops this way I would have to create 4 more loops. Currently macro takes about 25 minutes to run. If I do 4 seperate loops instead of doing multiple functions within 1 loop would this increase the processing time significantly?

 

[combo]

Working with ranges, esp. in loops, is slow. I tested the above code for 10000 cells, the cells were filled nearly immediately. The problem is with relative references in address string, if the target is a range with more than one cell, the formula in target behaves as if copied from the first cell.
You could consider redesigning the code, you can temporarily swith off screen updating (Application.ScreenUpdating), calculation (Application.Calculation) or event handling (Application.EnableEvents). You can also copy blocks of worksheet to variable (variant array), change it and return values to worksheet (those transfers can be done without loops too).

combo

 

[PHV]

In fact why a loop ?
Why not simply this ?
Range("U20:U" & (LR - 1)).Formula = "=SUM(E1:E20)/20"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886