percent change loop

[Eric]

Windows Excel 2003

Please excuse me, I'm going to have to go slowly
Any and all help is greatly appreciated. Can some
one suggest a strategy using several macros?
Breaking it up would make it easier for me to see
the parts and how they work together.

I have data in the following format:

A B C ... W
category 1 category 2 category 22

11 1960.01 49.8 53.3 161.8
12<empty row>
13 1960.02 49.8 53.2 161.1
14<empty row>
..
..
1069 2004.02 95.3 94.8 192.8
1070<empty row>

The mission is to find percent change for each
category, month on month.

I would like to return "1" for all categories in row 12,
assuming no change for interval 1959.12 to 1960.1

Otherwise, the goal is to compute the percent change
over the month interval for a category and write it
to the cell below, in the empty row.
For example, percentChange for category 1 over
interval 1960.01 to 1960.02 should be in B14.

Then move one cell right, repeat computation, move...
until wrapping around to A(currentRow plus 2). Continue
in such fashion to end of data, 1069W, with final
percentChange
entry in 1070W.

I would like to return "1" for all categories in row 12,
assuming no change for interval 1959.12 to 1960.1

For the unchanged case, like (B13-B11)/B11*100, the value
for
percentChange cannot be 100, but should be 0 instead.

And to make the sheet more readable, I want to add the
same
background color to all even numbered rows (containing the
percentChange numbers).

Thank you for your patience in reading to the end.
I would be most greatful for any and all responses.

Eric

 

[Frank Kabel]

Hi
see your reply in Excel.programming
P.S.: please don't multipost

 

[BrianB]

I think this is what you want :-

'--------------------------------------
Sub test()
Dim MyRange As Range
For rw = 14 To 1040 Step 2
ActiveSheet.Range("A" & rw).Interior.ColorIndex = 34
Set MyRange = ActiveSheet.Range(Cells(rw, 2), Cells(rw, 23))
MyRange.FormulaR1C1 = "=(R[-1]C-R[-3]C)/R[-3]C"
MyRange.NumberFormat = "0.00%"
MyRange.Interior.ColorIndex = 34
Next
End Sub
'---------------------------------------

 

[Eric]

Dear Frank,

Thanks for your reply here as well.

I am a reformed poster, and will not double post
again!

Eric