Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2014, 09:49 AM
tandchas tandchas is offline Macro not Performing Formula Update as Expected Windows 7 64bit Macro not Performing Formula Update as Expected Office 2010 64bit
Novice
Macro not Performing Formula Update as Expected
 
Join Date: May 2014
Posts: 7
tandchas is on a distinguished road
Default Macro not Performing Formula Update as Expected

On the attached file, the first item I am asking the "AddaShift" Macro to perform is copy row 17 and insert it directly above. While it does some other things properly, the only thing which I cannot get it to do is automatically update the formula in Cell J17. I would like for it to continue all the way to the last row which contains data but it keeps losing 1 row each time the macro runs. I assumed the formula would keep going to the last row with data but unfortunately not. Anybody have any suggestions?
Attached Files
File Type: xls PEUG (Rolling 30-Shift Average).xls (419.0 KB, 9 views)
Reply With Quote
  #2  
Old 06-25-2014, 10:58 AM
charlesdh charlesdh is offline Macro not Performing Formula Update as Expected Windows 7 32bit Macro not Performing Formula Update as Expected Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Change all of your pastspecials

Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
to
Code:
Selection.PasteSpecial Paste:=xlPasteAll
If you use "PasteValue" it will just place the value and not the formula.
Reply With Quote
  #3  
Old 06-25-2014, 11:41 AM
tandchas tandchas is offline Macro not Performing Formula Update as Expected Windows 7 64bit Macro not Performing Formula Update as Expected Office 2010 64bit
Novice
Macro not Performing Formula Update as Expected
 
Join Date: May 2014
Posts: 7
tandchas is on a distinguished road
Default

Charlesdh,

I appreciate your reply but that portion of the code is supposed to paste the value only; not the formula. The portion of the code which I want to copy the formula is:

Rows("17:17").Select
Selection.Copy
Rows("17:17").Select
Selection.Insert Shift:=xlDown

The formual in cell J17 currently goes to row 89. When the macro runs, it still only goes to row 89 but I would like it to go to row 90 since it has added a row.
Reply With Quote
  #4  
Old 06-25-2014, 12:17 PM
charlesdh charlesdh is offline Macro not Performing Formula Update as Expected Windows 7 32bit Macro not Performing Formula Update as Expected Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I see what you are talking about.
I'll see if I can come up with a solution.
It's possible another member may be able to also find s solution.
Reply With Quote
  #5  
Old 06-25-2014, 01:18 PM
charlesdh charlesdh is offline Macro not Performing Formula Update as Expected Windows 7 32bit Macro not Performing Formula Update as Expected Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I been playing around with this. But can nor find a solution in getting the code to change.
Unless some one else has an answer you may need to use a "Macro"
Reply With Quote
  #6  
Old 06-25-2014, 03:35 PM
charlesdh charlesdh is offline Macro not Performing Formula Update as Expected Windows 7 32bit Macro not Performing Formula Update as Expected Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I have a bit of code that may work. However I have to go for now. Will try to have it tomorrow.
Reply With Quote
  #7  
Old 06-26-2014, 11:38 AM
charlesdh charlesdh is offline Macro not Performing Formula Update as Expected Windows 7 32bit Macro not Performing Formula Update as Expected Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I modified your code. I left most of it in.
Copy and paste. Test on a copy. If it works then try it in your file.
Be sure you back up before you run on the actual data.

Code:
Sub AddADay()
'
' AddAShift Macro
'
'
Dim ws As Worksheet
Set ws = Sheets("PEUG")
lrow = ws.Range("D65536").End(xlUp).Row
    If MsgBox("Has ALL Data for Previous Shift Been Entered? If not, click 'No'.", vbYesNo) = vbNo Then Exit Sub
    ActiveSheet.Unprotect
    Rows("17:17").Copy
    Rows("17:17").Insert Shift:=xlDown
    Application.CutCopyMode = False
   Cells(17, 10).Formula = "=SUMPRODUCT(I17:I" & lrow & ",--((G17:G" & lrow & ")<31))/SUMPRODUCT(H17:H" & lrow & ",--((G17:G" & lrow & ")<31))"
   Cells(17, 17).Formula = "=SUMPRODUCT(P17:P" & lrow & ",--((N17:N" & lrow & ")<31))/SUMPRODUCT(O17:O" & lrow & ",--((N17:N" & lrow & ")<31))"
   Cells(17, 24).Formula = "=SUMPRODUCT(W17:W" & lrow & ",--((U17:U" & lrow & ")<31))/SUMPRODUCT(V17:V" & lrow & ",--((U17:U" & lrow & ")<31))"
   Cells(17, 31).Formula = "=SUMPRODUCT(AD17:AD" & lrow & ",--((AB:AB" & lrow & ")<31))/SUMPRODUCT(AC17:AC" & lrow & ",--((AB17:AB" & lrow & ")<31))"
   Cells(17, 38).Formula = "=SUMPRODUCT(AK17:AK" & lrow & ",--((AI17:AI" & lrow & ")<31))/SUMPRODUCT(AJ17:AJ" & lrow & ",--((AI17:AI" & lrow & ")<31))"

    Range("h17:i17").ClearContents
    Range("o17:p17").ClearContents
    Range("v17:w17").ClearContents
    Range("ac17:ad17").ClearContents
    Range("aj17:ak17").ClearContents
    Range("J18:K18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("q18:r18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("x18:y18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("ae18:af18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("al18:am18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
End Sub
Reply With Quote
  #8  
Old 06-27-2014, 05:42 AM
tandchas tandchas is offline Macro not Performing Formula Update as Expected Windows 7 64bit Macro not Performing Formula Update as Expected Office 2010 64bit
Novice
Macro not Performing Formula Update as Expected
 
Join Date: May 2014
Posts: 7
tandchas is on a distinguished road
Default

Thanks. Working well! Much obliged.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro not Performing Formula Update as Expected Performing filtering to tasks that occur during the next three months jessy Project 1 05-30-2014 05:40 AM
Macro not Performing Formula Update as Expected macro to update fields PeaceDove Word 3 01-17-2012 02:45 PM
Compare and Update Macro AaronMoss Excel Programming 2 05-06-2011 04:54 AM
Macro to update fields rhatx Word VBA 0 03-02-2011 12:14 PM
Macro not Performing Formula Update as Expected Excel 2011 not performing paste special correctly mzimmers Excel 1 01-04-2011 06:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft