Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 23rd 07, 10:34 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 10
Default look at multiple cells in a column and change a value

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

  #2   Report Post  
Old July 23rd 07, 10:56 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default look at multiple cells in a column and change a value

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

  #3   Report Post  
Old July 23rd 07, 12:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 10
Default look at multiple cells in a column and change a value

that helps alot thankyou very much , how would i do the same if my range is
on a different worksheet ? thankyou

"Toppers" wrote:

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

  #4   Report Post  
Old July 23rd 07, 01:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default look at multiple cells in a column and change a value

This code needs to be on the same worksheet as the data that is changed;
hence why you have to select the tab and copy the code.

"RobG2007" wrote:

that helps alot thankyou very much , how would i do the same if my range is
on a different worksheet ? thankyou

"Toppers" wrote:

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change shade of cells when selecting multiple cells abrummet Excel Discussion (Misc queries) 3 September 6th 07 11:42 AM
macro to change multiple values in a column mrwawa Excel Discussion (Misc queries) 7 July 2nd 06 04:35 PM
Sum cells in multiple worksheets which change every month mnew27 Excel Discussion (Misc queries) 3 June 23rd 06 10:04 AM
Can I change the names of multiple cells at once? shadestreet Excel Discussion (Misc queries) 8 May 31st 06 12:55 AM
Change a Column list into multiple rows & columns angelface Excel Worksheet Functions 3 January 28th 06 02:23 AM


All times are GMT +1. The time now is 02:04 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017