VBA not overwriting cells

[mdavid800]

HI I have this code and it is averaging every 12 cells.

 Sub avg_sun()
Dim i As Long

For i = 35 To 8674 Step 12

Range("CC" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Worksheets("sun").Range("BP" & i).Resize(12))

Next i

End Sub

However I have noticed a problem. When the code is run the first time and the destination cells were the averages are printed to are blank the code works correctly.

However if the code is run for a second time instead of overwriting the answers already in the cells the new answers are placed at the end

For instance if the first time run the code puts the averages in cells C2 to C722 the next time the code is run instead of overwriting C2 to C722 It places the averages in cells C723 to 1443.

Any idea how I can change this code to over right the cells.

Cheers

David

[barryleajo]

Why not clear the column before entering the loop?

Columns("CC").ClearContents

[HSV]

Or:

 Sub avg_sun()
Dim i As Long

Range("CC2:CC" & Range("CC" & Rows.Count).End(xlUp).Row).ClearContents
For i = 35 To 8674 Step 12

Range("CC" & Rows.Count).End(xlUp).Offset(1).Value = WorksheetFunction.Average(Worksheets("sun").Range("BP" & i).Resize(12))

Next i

End Sub

[mdavid800]

Thanks guys, nice simple solution

cheers

ps. how do you mark these threads as solved

[barryleajo]

See Forum Rules No.9

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it