Macro avoiding circular reference
 

I need a macro that will do the following:
Take the cell highlighed and the next 29 cells in that row,
Subtract the value of A1 from each of these cells and paste the new value
(not the formula) into the same 30 cells,
Do this only once.

Thanks for any help.

suppose Public Sub your data is in C1 to C30
try this sub

public sub test()
Dim cell As Range
For Each cell In Range("c1:c30")
cell = cell - Range("a1")
Next
End Sub
====
perhaps there are more elegant ways.


Kanga 85 wrote in message
...
I need a macro that will do the following:
Take the cell highlighed and the next 29 cells in that row,
Subtract the value of A1 from each of these cells and paste the new value
(not the formula) into the same 30 cells,
Do this only once.

Thanks for any help.

One way:

Public Sub SubtractA1()
Range("A1").Copy
ActiveCell.Resize(1, 30).PasteSpecial _
Paste:=xlValues, _
Operation:=xlSubtract, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End Sub


In article ,
"Kanga 85" wrote:

I need a macro that will do the following:
Take the cell highlighed and the next 29 cells in that row,
Subtract the value of A1 from each of these cells and paste the new value
(not the formula) into the same 30 cells,
Do this only once.

Thanks for any help.

Thanks,
This looks simple and repects my active cell.

"JE McGimpsey" wrote:

One way:

Public Sub SubtractA1()
Range("A1").Copy
ActiveCell.Resize(1, 30).PasteSpecial _
Paste:=xlValues, _
Operation:=xlSubtract, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End Sub


In article ,
"Kanga 85" wrote:

I need a macro that will do the following:
Take the cell highlighed and the next 29 cells in that row,
Subtract the value of A1 from each of these cells and paste the new value
(not the formula) into the same 30 cells,
Do this only once.

Thanks for any help.