# Can anyone think of a better way of doing this?

#### fidgen

##### New Member
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

Replies
1
Views
406
Replies
0
Views
507
Replies
8
Views
1K
Replies
1
Views
570
Replies
8
Views
527

1,218,790
Messages
6,144,506
Members
450,550
Latest member
Melanie_Annies

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back