I have a programing problem!

[mgmcdevitt]

I have built a macro using the recording function. The programing was intended to have cell B20 equal to cell B197. Here is the program

Sub AZWage()
'
' AZWage Macro
' Macro recorded 8/19/2005 by Systems Administrator
'

'
Range("G9:G13").Select
Selection.Interior.ColorIndex = xlNone
Range("G12").Select
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[177]C"
Range("B21").Select
ActiveCell.FormulaR1C1 = "=R[178]C"
Range("B23").Select
ActiveCell.FormulaR1C1 = "=R[173]C"
Range("B24").Select
ActiveCell.FormulaR1C1 = "=R[174]C"
Range("B26").Select
ActiveCell.FormulaR1C1 = "=R[169]C"
Range("B27").Select
ActiveCell.FormulaR1C1 = "=R[167]C"
Range("B28").Select
Range("C20").Select
ActiveCell.FormulaR1C1 = "=R[177]C"
Range("C21").Select
ActiveCell.FormulaR1C1 = "=R[178]C"
Range("C23").Select
ActiveCell.FormulaR1C1 = "=R[173]C"
Range("C24").Select
ActiveCell.FormulaR1C1 = "=R[174]C"
Range("C26").Select
ActiveCell.FormulaR1C1 = "=R[169]C"
Range("C27").Select
ActiveCell.FormulaR1C1 = "=R[167]C"
Range("C28").Select
ActiveWindow.ScrollRow = 1
End Sub

The problem with the prgram is that if you insert cells above B20 or between B20 and B197 then everything gets goofed up. What can I do differently so that if the worksheet is modified it doesn't effect the macro.

Thanks for the help. I need it as soon as possible. I am trying to help the big boss out with this.

[windsurferLA]

mgmcdevitt wrote:
> I have built a macro using the recording function. The programing was
> intended to have cell B20 equal to cell B197. Here is the program
>
> Sub AZWage()
> '
> ' AZWage Macro
> ' Macro recorded 8/19/2005 by Systems Administrator
> '
>
> '
> Range("G9:G13").Select
> Selection.Interior.ColorIndex = xlNone
> Range("G12").Select
> With Selection.Interior
> .ColorIndex = 41
> .Pattern = xlSolid
> End With
> Range("B20").Select
> ActiveCell.FormulaR1C1 = "=R[177]C"
> Range("B21").Select
> ActiveCell.FormulaR1C1 = "=R[178]C"
> Range("B23").Select
> ActiveCell.FormulaR1C1 = "=R[173]C"
> Range("B24").Select
> ActiveCell.FormulaR1C1 = "=R[174]C"
> Range("B26").Select
> ActiveCell.FormulaR1C1 = "=R[169]C"
> Range("B27").Select
> ActiveCell.FormulaR1C1 = "=R[167]C"
> Range("B28").Select
> Range("C20").Select
> ActiveCell.FormulaR1C1 = "=R[177]C"
> Range("C21").Select
> ActiveCell.FormulaR1C1 = "=R[178]C"
> Range("C23").Select
> ActiveCell.FormulaR1C1 = "=R[173]C"
> Range("C24").Select
> ActiveCell.FormulaR1C1 = "=R[174]C"
> Range("C26").Select
> ActiveCell.FormulaR1C1 = "=R[169]C"
> Range("C27").Select
> ActiveCell.FormulaR1C1 = "=R[167]C"
> Range("C28").Select
> ActiveWindow.ScrollRow = 1
> End Sub
>
> The problem with the prgram is that if you insert cells above B20 or
> between B20 and B197 then everything gets goofed up. What can I do
> differently so that if the worksheet is modified it doesn't effect the
> macro.
>
> Thanks for the help. I need it as soon as possible. I am trying to help
> the big boss out with this.
>
>
This is a general answer to your question. I'm not providing code.

The code you recorded has absolute rather than relative addressing.
Let's look at:

> Range("B20").Select
> ActiveCell.FormulaR1C1 = "=R[177]C"

This code of yours is going to enter the formula "=R[177]C" into cell
"B20" independent of whatever else is going on in the workbook. If you
add rows before row 20, the macro will still enter the formula in row 20
column two.

When you add and take away rows in an Excel sheet, Excel automatically
adjusts the row pointers to compensate for the change. When you add and
take away rows in an Excell sheet, no changes are made to the macro
modules, and thus they macro modules may point to places that have since
moved.

If you want the macros to work independent of row additions or
subtractions, you have to construct a relative addressing system. If you
know one cell is going to remain unchanged, you possibly can get away
with recording a macro using "relative" addressing that starts in that cell.

alternatively, you need to place an anchor in your sheet, add a macro to
search for than anchor and record its location, and then compute
addresses relative to that anchor.

[mgmcdevitt]

I tried the relative button and that doesn't quite do the trick. I am not sure how to perform the second operation that was suggested. Isn't there an easier way to do it?