Can anyone think of a better way of doing this?

[fidgen]

Hiya, I need a macro that scans a range of cells, having found the reference cell by a (B, C) reference, and calculate the MAX.

I've done the following for 7 days around a middle point, (3 days either side of (B,C) but I'm looking to do it for a years worth of data! (132 cells either side of the data point)

Worksheets("strikes2").Cells(A, 108) = Worksheets("data").Cells(B, C)
Worksheets("strikes2").Cells(A, 109) = Worksheets("data").Cells((B + 1), C)
Worksheets("strikes2").Cells(A, 110) = Worksheets("data").Cells((B + 2), C)
Worksheets("strikes2").Cells(A, 111) = Worksheets("data").Cells((B + 3), C)
Worksheets("strikes2").Cells(A, 112) = Worksheets("data").Cells((B - 1), C)
Worksheets("strikes2").Cells(A, 113) = Worksheets("data").Cells((B - 2), C)
Worksheets("strikes2").Cells(A, 114) = Worksheets("data").Cells((B - 3), C)
Cells(A, 45).Select
ActiveCell.FormulaR1C1 = "=MAX(RC[63]:RC[69])"
This message was edited by fidgen on 2002-10-07 08:45

 

[Guest]

Hiya, I need a macro that scans a range of cells, having found the reference cell by a (B, C) reference, and calculate the MAX.

I've done the following for 7 days around a middle point, (3 days either side of (B,C) but I'm looking to do it for a years worth of data! (132 cells either side of the data point)

Worksheets("strikes2").Cells(A, 108) = Worksheets("data").Cells(B, C)
Worksheets("strikes2").Cells(A, 109) = Worksheets("data").Cells((B + 1), C)
Worksheets("strikes2").Cells(A, 110) = Worksheets("data").Cells((B + 2), C)
Worksheets("strikes2").Cells(A, 111) = Worksheets("data").Cells((B + 3), C)
Worksheets("strikes2").Cells(A, 112) = Worksheets("data").Cells((B - 1), C)
Worksheets("strikes2").Cells(A, 113) = Worksheets("data").Cells((B - 2), C)
Worksheets("strikes2").Cells(A, 114) = Worksheets("data").Cells((B - 3), C)
Cells(A, 45).Select
If you don't need the values listed again, and assuming C is a string and B is an integer, then
ActiveCell.Formula = "=MAX(data!" & C & B - 132 & ":" & C & B + 132 & ")"

F.T.