I have built a macro using the recording function. The programing wa
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 o
between B20 and B197 then the everything gets goofed up. What can I d
differently so that if the worksheet is modified it doesn't effect th
macro.

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

--
mgmcdevit
-----------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread.php?threadid=39860

hi,
curious. why are you doing this with a macro? if the programing was
intended to have cell B20 equal to cell B197, this might be better done with
formulas.
in cell b20 put =$b$177 and so on.
this way if b197 changed in value, it would automaticly reflect in B20.
if rows were added between b20 and b197, the absolute reference would not
change.
if i'm missing something here, reply with more info.

Regards
FSt1

"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 the 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.


--
mgmcdevitt
------------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...o&userid=26592
View this thread: http://www.excelforum.com/showthread...hreadid=398608

It is a complicated explanation, but I have about 20 similar macro
written. Each of the macros are initiated by pressing an object in
table. Then the corresponding values for what you have selected ar
entered in the cells.

For Example: Our company has offices in CA, CO, and NM. Each offic
uses different wages for each of their employees. Each office is usin
the same worksheet and I want each office to be able to select thei
office in the table and their wages to be used in the spreadsheet. Whe
the office selects their office in the table by pressing the butto
their wages are pulled from a large table with the info from all th
offices

--
mgmcdevit
-----------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread.php?threadid=39860

hi.
sorry so late getting back. i usually to ms groups while at work.

i still see no solution for you. the adding rows in the key. code just does
what it's told. it doesn't keep records about the number of rows that may
have been added or removed sometime in the past and automaticly compensate.
I'm sure it could be done by identifing B177 from maybe the bottom and as a
variable instead of a hard reference but without seeing the data i don't know
what to suggest.
if you want, send me the file and i will see what i can do.



Regards
FSt1


"mgmcdevitt" wrote:


It is a complicated explanation, but I have about 20 similar macros
written. Each of the macros are initiated by pressing an object in a
table. Then the corresponding values for what you have selected are
entered in the cells.

For Example: Our company has offices in CA, CO, and NM. Each office
uses different wages for each of their employees. Each office is using
the same worksheet and I want each office to be able to select their
office in the table and their wages to be used in the spreadsheet. When
the office selects their office in the table by pressing the button
their wages are pulled from a large table with the info from all the
offices.


--
mgmcdevitt
------------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...o&userid=26592
View this thread: http://www.excelforum.com/showthread...hreadid=398608