# 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:RC)"
This message was edited by fidgen on 2002-10-07 08:45

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
G

#### Guest

##### 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.

Replies
2
Views
143
Replies
5
Views
238
Replies
1
Views
920
Replies
2
Views
686
Replies
2
Views
70

### Forum statistics

1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm ### 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