Can anyone think of a better way of doing this?

fidgen

New Member
Joined
Aug 20, 2002
Messages
46
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

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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top