View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Otto Moehrbach Otto Moehrbach is offline
external usenet poster
 
Posts: 1,090
Default subtracting from multiple cells

Assuming the range you want to change is A1:A6 and the remote cell is H1 and
they are all on the same sheet, the following macro does what you want.
Note that this macro is a sheet macro and must be placed in the sheet module
of the sheet in question. To access that sheet, right-click on the sheet
tab, select View Code. Paste this macro into the displayed module. "X" out
of the module to return to the sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "H1" Then
Target.Copy
Range("A1:A6").PasteSpecial Paste:=xlPasteAll,
Operation:=xlSubtract
End If
End Sub

"He cries for help" <He cries for wrote in
message ...
I am trying to subtract a given number from numerous cells i.e.
A1,A2,A3,-6.
I want to do this from a remote cell by just entering a number. This cell
will be
used to update these cells (i.e. A1,A2,A3) on a daily basis.

The target cells should update with each entry.

Thanks in advance, as you can tell I am a Novice. I am more of a access
user.