=SUM Ranges Do Not Update

I have a Excel 2000 spread sheet, with the following macro to insert 
new row.

Sub InsertRow()
'
' Macro1 Macro
' Macro recorded 4/27/2004 
'

'GoTo label, MyString
ActiveSheet.Unprotect
Application.Goto Reference:="MyCell"
ActiveCell.Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveCell.Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

End Sub

The problem is that in the "Mycell" row I have some =SUM ranges an
when I run the macro the ranges do not update to include the ne
cells.

Exsample:
=SUM(M10:M52)
New row now 53
needs to read =SUM(M10:M53), etc.

Thank

--
Message posted from http://www.ExcelForum.com

0
8/23/2004 4:37:49 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1287 Views

Similar Articles

[PageSpeed] 17

in the column that has the formula =sum(M10:M53).
at cell(M10) change the formula to =sum($M$10:M10) then 
copy down. that should fix your problem.
$M$10 is an absolute reference meaning it never changes.
m10 is a relitive reference to the cells around it and 
changes if you copy formulas and move them. copy a formula 
at m10 and paste it at m20 the reference will change to 
m20. copy $m$10 and paste it at m20, it's still $m$10.
>-----Original Message-----
>I have a Excel 2000 spread sheet, with the following 
macro to insert a
>new row.
>
>Sub InsertRow()
>'
>' Macro1 Macro
>' Macro recorded 4/27/2004 
>'
>
>'GoTo label, MyString
>ActiveSheet.Unprotect
>Application.Goto Reference:="MyCell"
>ActiveCell.Select
>ActiveCell.EntireRow.Insert
>ActiveCell.Offset(-1, 0).Select
>ActiveCell.EntireRow.Copy
>ActiveCell.Offset(1, 0).Select
>ActiveCell.PasteSpecial xlPasteAll
>Application.CutCopyMode = False
>ActiveCell.Select
>ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
>Scenarios:=True
>
>End Sub
>
>The problem is that in the "Mycell" row I have some =SUM 
ranges and
>when I run the macro the ranges do not update to include 
the new
>cells.
>
>Exsample:
>=SUM(M10:M52)
>New row now 53
>needs to read =SUM(M10:M53), etc.
>
>Thanks
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
8/23/2004 6:15:19 PM
Thank you Frank for your reply.

But this does not solve my problem.
I understand about absolute and relitive reference.
I'll try to explain, 
The =SUM(M10:M52) is in the M53 cell and the data I need to sum is i
cells from M10 to M52. When you run the macro it inserts a new ro
above row 53 or Mycell row. Now my function is in M54 and stil
refering to, =SUM(M10:M52) and it should read =SUM(M10:M53).

Thanks agai

--
Message posted from http://www.ExcelForum.com

0
8/24/2004 12:27:29 AM
Reply:

Similar Artilces:

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

How do I do this UPDATE in a single query?
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1' WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ; UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1' WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ; UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1' WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ; UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1' WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ; UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66]...

UPDATE RM20101 on posting SOP invoice
Hi, I am wanting to have the RM20101 Description field be updated with the SOP10100.refrence field when the SOP invoice\return is posted. Currently the description field gets populated with the SOPNumber. A combination of batch and transaction posting is used, and when whatever is done - I only want it done for the current set of SOP docs that are being posted. Please advise the best way (and steps or script if possible) this can be done - without Dexterity: 1) VBA (maybe on the SOP Posting Journal) 2) Trigger on the RM20101 3) SQL Job 4) Anything else that I have not listed Thank you ...

Update type conversion error with nulls
I need to simply divide one currency value by another currency value. Sounds simple????? Here are two sql statements I've tried. Most records are null, it is only the ones that I want to update and close that will have a value in Net_PL. UPDATE Diagonal SET Diagonal.Final_ROR = IIf([Diagonal]![Net_PL] Is Null,0,[Diagonal]![Net_PL]/[Diagonal]![Tr_Trade_Risk]); UPDATE Diagonal SET Diagonal.Final_ROR = Nz([Diagonal]![Net_PL])/Nz([Diagonal]![Tr_Trade_Risk]); I get the error message "...didn't update x number of fields due to a type conversion error......."...

Can't update private meeting on room resource
I have a room resource that everyone has author access to. One of the users keeps marking the meetings as Private. When he needs to change something on the appointment, it shows the following messages: One or more recipients in this item were not usable. You need to recreate this address. You don't have appropriate permission to perform this operation. I have to open the mailbox of the resource and update the meeting. Then the user has to remove the resource from the update request. Is this normal? Do I have the permissions set incorrectly? ...

Help
CRM 3.0 refuses to install on my SBS 2003 R2 premium server. I was encountering the known issues with CRM 3 on SBS R2 using the CDs that came in the Action Pack subscription so I downloaded the updated CRM CDs from http://www.microsoft.com/downloads/details.aspx?FamilyID=7d418781-69ad-422d-92fa-87fdb2538e2c&DisplayLang=en This copy gave me a different set of problems. The default setup mode pops up a couple errors. The first one is the lack of full text search so I installed that and ran the setup again. The next error was: "The edition 'Workgroup Edition' of the specified SQL...

How to update a link field with preserve format using VBA?
HI All, How to update a link field with preserve format using VBA? Anthony Without knowing more about what you are trying to do it is difficult to advise. You could for example switch to print preview and back or you could use the example code at http://www.gmayor.com/installing_macro.htm both of which will update fields in your document. If, as your e-mail address suggests, this is a link to an Excel range then I suspect your concern may be more to do with maintaining the formatting than updating the field. That will depend on the type of link you have inserted. If you ...

Cannot send updates for existing reoccurring meetings
Hello All, Some users I support are having an issue when attempting to change, then send out an update on existing reoccurring meetings in their managers calendar. Currently we are running in a AD domain with Exchange 5.5 and Outlook 2000 as the client. What's happening is this: When the original reoccurring meeting was created it was done by a user who rights were not correct for that calendar. Now that these rights have been modified to be correct the reoccurring meeting does not allow an update to be sent such as a date change or time change. When this is attempted the "Cl...

Money not updating online
It seems that Money goes to www.spcomstock.com to get quote updates (look in my information sheet) ... however that site has change to www.comstock- interactivedata.com. Now online updating in Money doesn't work ... at least mine doesn't. Try to go to the www.spcomstock.com and you'll time out with an error. I don't see any way to change how money accesses the web site. Thoughts .... am I off base. Have read other notes on the subject and suggestions around firewalls etc. I haven't changed mine, but just in case turned it off and still money won't update....

SUM of Numbers with colons (:) such as Hours & Minutes
Does anyone know how to get the SUM of a column with numbers formated as Time (hh:mm) useing the SUM formula to get the total numbers of hours in that column. For example: Day-1 10:45 Day-2 11:05 Day-3 09:10 Total 31:00 Help is very appreciated. Wally Hi Wally for your formula use =SUM(B1:B3)*24 and format it as a number (format / cells - number) Cheers JulieD "CLARKSON PHARMACY" <CLARKSON.PHARMACY@VERIZON.NET> wrote in message news:XaCZd.3302$ed6.530@trndny06... > Does anyone know how to get the SUM of a column with numbers formated as...

Keyboard shortcut for editing links with updating
Can some one post the keyboard shortcut: When editing a link in a worksheet I don't want it update with new value when I hit Enter because it takes long to update(going over anetwork) and have many links to edit. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi there, There isn't a shortcut that I know of per se - what you can do is go to Tools-->Options on the Calculation tab change it from Automatic to Manual. This will apply to all links and calculatio...

How to sum all payments in database and show on form
How to sum all payments in database and show on form? I posted a question yesterday about my form, and have a new question about the same form. I have a form frmVendor with a subform frmPayment. They are linked to tables tblVendor and tblPayments (which are related by VendorID). The form frmVendor shows one vendor at a time, with subform showing only payments made to that vendor. I want to put a calculated field in the main form (frmVendor) header showing the total of all payments in the tblPayments (not just the total for the vendor currently being viewed.) Reason being, Company ha...

Alert for modification/update of cases
I have recently installed CRM 3 professional and i've come across a feature that our sharepoint server has that CRM doesn't and i would like to know if it is possible to achieve this. Anytime anyone updates or changes something on sharepoint list/discussion board the user can opt to have an email sent to them informing them that something has changed on that particular board or list. Can the same thing be done in CRM for cases? -- Regards, Matt Yes, you could do this with a post-callout. -- Matt Parks MVP - Microsoft CRM "Matt" <Matt@discussions.microsoft.com...

Summing with Spaces
Hi, If I have a column of numers like this: 3 4 4 3 2 1 3 2 Each group of numbers represents a different day seperated by a space. In the adjacent column I want to do a running total of each group so I have a running subtotal for each day, so it would look like this: 3 3 4 7 4 11 3 3 2 5 1 6 3 3 2 5 The formula looks complicated. Thanks for any help. Make sure the first line is empty I assume that with "a space" you mean an empty row. If your data is in Column A (starting in a2), put this formula in B2: =IF(A2="",0,B1+A2) And copy down as far as you ne...

Updating treeview control in form
I have an Access 2007 form that has a treeview ActiveX control that lists the dates and names of scheduled events. I have a popup form to edit and or delete an event. On the popup form there is a command button for saving any edits and a command button to delete a selected event. In both cases I need code to refresh the event list displayed in the treeview control of the event form after closing the popup form. I keep playing with refresh and requery methods but can't seem to figure it out. Any help will be greatly appreciated. Ken ...

non vba way to print non continuous ranges
Hi, Is there a non vba way to print non continous ranges on the one shee using excel? Specifically, lets say I want to print data from columns A, B, C, G an K down to row 35 in each column. Is there a way I can do that withou manually making them continuous by cutting and pasting each range ont a new sheet? Regards, David Obei -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 You can h...

Summing certain values only in a report
I have a report where I want to Sum the qty of sales of a certain product but I also want to display the Sum of total sales (dollar value). ProductX_Qty Total Sales ($) 120 $55000 In this example the total dollar value of the 120 pcs may not total $55000 due to parts sales that I don't want to count. I have a column in the query where it is described as either a "Parts" order or a "Unit" order. -- TIA Try an expression like this in the Control Source of a text box in the Report Footer section: =Sum(IIf([ProductID]="X", [...

How do you sum on top of a group by
How do you do a sum on top of a group by or how do you combine an if statement with a group by? My code looks like this: SELECT t_Starts_AppFlow.[Organization Level 1 : R], t_Starts_AppFlow.[Location Level 3 : R], t_Starts_AppFlow.[EEO/AAP], Count(t_Starts_AppFlow.[Step Name : CSW]) AS Hire, Sum(t_Starts_AppFlow!Female) AS [Hire Female], Sum(t_Starts_AppFlow!POC) AS [Hire POC], Sum(t_Starts_AppFlow!Manager) AS [Hire Manager], Sum(t_Starts_AppFlow![Non-Manager]) AS [Hire Non-Manager], Sum(t_Starts_AppFlow!Engineer) AS [Hire Engineer], Sum(t_Starts_AppFlow![Non-Engineer]) AS ...

Security price updates.
I have noticed that my price updates for stocks etc. have been slow and intermittent over the last week or so. Is this further evidence that Microsoft has lost interest in supporting Money. I am running the current version of Money Plus Deluxe. ...

How do I advance the range by one row Excell VBA automatically #2
I am using Sheets(Array("Breakfast", "Lunch", "Supper", "Bedtime")).Select Range("B94:D98").Select but when I run this more than once it overwrites what was put in the first time. ...

Updating mailNickname (ms-Exch-Mail-Nickname) with the contents of samAccountName (SAM-Account-Name)
I currently have an issue where a good chuck of my e-mail accounts all have Alias fields in Exchange that match the AD login account. However my counterpart administrators in another country have not followed this procedure and therefore their alias fields are many different things with no standardization. I've been asked to find a way to get all exchange accounts alias fields to match their AD login account. If anyone knows anyway (i.e. - script, tool) way to do this I would greatly appreciate your help. Thanks, Mark "Stingray" <ks_stingray@hotmail.com> wrote: >I...

Function to Use when compare sums another column
I have and age Field and a Column that contain counts. If the aging Column contains a number between >=0 and <=30 I want it to sum up the counts that correspond in field B2. I tried the following and keep getting invalid returns. =SUM(IF(MATCH("<=30",Sheet2!F:F,0),INDEX(Sheet2!B:B,MATCH("<=30",Sheet2!F:F)),0)) One way...(I think)... =sumif(a:a,">="&0,b:b) - sumif(a:a,">"&30,b:b) Heather wrote: > > I have and age Field and a Column that contain counts. > If the aging Column contains a number bet...

Summing Subtotals
I have a worksheet with numerous subtotals and a grandtotal. The subtotals are separated by several individual entries for each subtotal. I have tried selecting each subtotal and the grand total cell, but when I hit enter it shows only the last subtotal. What am I doing wrong? Thanks for your help! -- jim Jim, To sum a column of a sheet with subtotals included, you can use =SUM(B:B)/2 OR =SUM(B:B)/3 Since you have the grandtotal showing... But you really shouldn't need to do that. Describe better what it is that you are looking to do, and we can come up with a better way - Piv...

Can;t update
I purchased Office 2004 for Teachers, and I want to update from 11.2.5 to 11.3.5. The download starts, but then loses the connection. I posted about this before and was told to contact my ISP. They say that since everything else works, the problem has to be the download site. I've tried at least 20 times-no luck. Please help. Thank you very much. In article <C33BBE9D.551%efbarton@roadrunner.com>, efb <efbarton@roadrunner.com> wrote: > I purchased Office 2004 for Teachers, and I want to update from 11.2.5 to > 11.3.5. The download starts, but then loses the connectio...

Naming ranges?
Hi, I have no problem naming ranges of cells in XL. However, I have the following scenario that I can't find a solution to: Imagine you have 5 identical worksheets. You want to highlight a data range which is also indentical in each worksheet. Can you create 1 range name that applies to a range of cells on each worksheet, rather than having to create 5 different named ranges (1 for each worksheet)? It is odd because if you have 1 worksheet, name a range, and then make 4 copies, they will all have the same range under the same name - so it must be possible to do it the other way a...