MS ecel 2010宏失败 - 无法更新单元格的公式 [英] MS ecel 2010 macro fails - unable to update cell's formula
问题描述
更新单元格公式的我的MS Excel宏(子例程)开始失败(在最新的Microsoft更新2017-03-10之后)。现在当这部分代码运行MS Excel暂停时,然后尝试恢复。导致停止的代码行是使用新的/更改的公式重新分配回单元格。我通过指向一个空单元格测试了代码行,并且运行成功。将其指向已填充代码的单元格失败。我没有找到解决方案,谷歌广泛可能的原因。非常感谢您的帮助。下面的代码片段显示了我正在做的事情:
代码片段:
在注册中插入新行;按下行;自动更新行号
' iRowInsert代码插入新行(oWS1.Range(A5) ).EntireRow.Insert)
调用 iRowInsert
Dim oWS1,oWS2,i,j,k,orgFormula,newFormula
Dim oRNG,maxRows,maxCols,rowStart,rowCurrent
设置 oWS1 =工作表( 注册)
设置 oWS2 =工作表( 预算)
' 识别范围起始行号
rowStart = 5
rowCurrent = rowStart
' R. etrieve cell的公式
orgFormula = oWS2.Cells(rowCurrent, 4 )。公式
' orgFormula是= SUMIFS(注册!$ I $ 6:$ I412,注册!$ K $ 6:$ K412,D $ 1,注册!$ H $ 6:$ H412,$ A5)
' 替换原始公式的起始行号
newFormula = Replace(newFormula, $ 6:, $ 4:)
oWS2.Cells(rowCurrent, 4 ).Locked = False ' 真的不需要 - 工作簿没有受保护
' *******在重新设置填充单元格的公式时失败*****
oWS2.Cells(行当前, 4 )。公式= newFormula
' ************************************************* ***********
' oWS2.Cells(rowCurrent, 3).Value = newFormula'在插入空单元格时正常工作
oWS2.Cells(rowCurrent, 4 )。锁定= True ' 真的不需要 - 工作簿不受保护
我的尝试:
( 1)谷歌类似的问题并审查了建议的结果
(2)双/三格式格式的内容和结构 -
(3)针对空单元格的Ran代码 - 成功插入
(4)针对填充单元格的代码 - 失败
I
6:<!/ BLOCKQUOTE>
I412,注册
My MS Excel Macro (subroutine) that updates cell formula began to fail (after the latest Microsoft update 2017-03-10). Now when this section of code runs MS Excel halts then tries to recover. The line of code causing the stoppage is the re-assignment back to the cell with the new/changed formula. I tested the line of code by pointing to an empty cell and it runs successful. Pointing it back to a cell already filled the code fails. I've google extensively possible causes without finding a solution. Your assistance is greatly appreciated. Below code snippet shows what I've am doing:
Code snippet:
' Insert new row in "Register"; Push down rows; automatic update row numbers ' iRowInsert code inserts new row (oWS1.Range("A5").EntireRow.Insert) Call iRowInsert Dim oWS1, oWS2, i, j, k, orgFormula, newFormula Dim oRNG, maxRows, maxCols, rowStart, rowCurrent Set oWS1 = Worksheets("Register") Set oWS2 = Worksheets("Budget") ' Identify range beginning row number rowStart = 5 rowCurrent = rowStart ' Retrieve cell's formula orgFormula = oWS2.Cells(rowCurrent, 4).Formula ' orgFormula is "=SUMIFS(Register!$I$6:$I412,Register!$K$6:$K412,D$1,Register!$H$6:$H412,$A5)" ' Replace original formula's starting row number newFormula = Replace(newFormula, "$6:", "$4:") oWS2.Cells(rowCurrent, 4).Locked = False ' really not needed - workbook not protected ' ******* fails when re-setting a filled cell's formula ***** oWS2.Cells(rowCurrent, 4).Formula = newFormula ' ************************************************************ ' oWS2.Cells(rowCurrent, 3).Value = newFormula ' works correctly when inserted into empty cell oWS2.Cells(rowCurrent, 4).Locked = True ' really not needed - workbook not protected
What I have tried:
(1) Google similar problems and reviewed suggested results
(2) Double/Triple checked format's content and structure -
(3) Ran code against an empty cell - Successful insert
(4) Ran code against a filled cell - Failed解决方案I
6:
I412,Register!
这篇关于MS ecel 2010宏失败 - 无法更新单元格的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!