- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个大型 Microsoft Excel 文件,在办公室的用户之间共享。该文件有一个宏和一组数据验证。该文件每三个月的最后两周使用一次。该文件在圣诞节之前工作得很好,但是,我们在三月份遇到了错误。打开该文件时出现以下错误。
点击yes后,出现以下内容。
宏的代码是:
Sub Update()
'Declaring Variables.
Dim Number_Rows As Long
Dim Oppt As String
Dim Array_Oppt() As String
Dim Rows_Array As Integer
'Stops screen refreshing to save time.
Application.ScreenUpdating = False
'removing any data validation and conditional formatting
Sheet1.Activate
Cells.Select
Selection.Validation.Delete
Selection.FormatConditions.Delete
'Assigning value to array.
Rows_Array = 0
ReDim Preserve Array_Oppt(Rows_Array)
'Moving "Other" rows to the end of the data
Sheet1.Select
Selection.AutoFilter Field:=23, Criteria1:= _
"OTHER"
Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
Selection.EntireRow.Select
Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet8.Select
Sheet8.Range("A1").Select
ActiveSheet.Paste
Sheet1.Select
Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
Selection.EntireRow.Select
Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Number_Rows = Application.WorksheetFunction.CountA(Range("A:A"))
Sheet8.Select
Selection.Cut
Sheet1.Activate
Range("A" & Number_Rows + 1).Select
ActiveSheet.Paste
'Counting number of rows.
Number_Rows = Application.WorksheetFunction.CountA(Range("A:A"))
'Loop to check if oppt if unit or non unit deal. Checks if oppt is in array and adds oppt if not.
'Calculates total revenue per opp.
For i = 2 To Number_Rows
Oppt = Range("I" & i)
'In array already.
If UBound(Filter(Array_Oppt, Oppt)) >= 0 Then
'Non Unit deal in the array already. Deletes line and shifts row up as well as i up.
If Range("W" & i) = "OTHER" Then
Rows(i).Select
Selection.Delete Shift:=xlUp
Number_Rows = Number_Rows - 1
i = i - 1
'Unit deal in the array already.
Else
Range("J" & i) = ""
End If
'Not in array so needs to be added.
Else
Range("J" & i) = Application.WorksheetFunction.SumIf(Range("I:I"), Oppt, Range("J:J"))
If Range("W" & i) = "OTHER" Then
Range(Cells(i, 19), Cells(i, 26)) = ""
Else
End If
'Redefines the size of the array factoring in new added row.
ReDim Preserve Array_Oppt(Rows_Array)
Array_Oppt(Rows_Array) = Oppt
Rows_Array = Rows_Array + 1
End If
Next
'Adds blank column for business manager.
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1") = "Business Manager"
'Updates the titles of the last columns in file
Range("AB1") = Date - 2
Range("AC1") = Date - 1
Range("AD1") = "Today"
Range("AE1") = "Focus List"
Range("AF1") = "% Chance"
Range("AG1") = "Allocation Status"
Range("AH1") = "New PO Date"
Range("AI1") = Date - 3
Range("AJ1") = Date - 4
Range("AK1") = Date - 5
Range("AL1") = Date - 6
Range("AM1") = Date - 7
Range("AN1") = Date - 8
Range("AO1") = Date - 9
Range("AP1") = Date - 10
Range("AQ1") = "Partner Grouping"
Range("AR1") = "VNX Models"
Range("AS1") = "Commit + X"
Range("AT1") = "Country"
Range("AU1") = "Theater"
'Moves to Sheet2 and copies Upside X column B to the end (column AV) for the purpose of VLookup. Then returns to Sheet1.
Sheet2.Activate
Sheet2.Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheet2.Columns("AV:AV").Select
ActiveSheet.Paste
Sheet1.Activate
'Loop to add Linked status; and to complete VLookups from yesterday.
For i = 2 To Number_Rows
'If to check linked status
If Range("L" & i) = "" Then
Range("M" & i) = "Not Linked"
Else
Range("M" & i) = "Linked"
End If
'Vlookups
On Error Resume Next
Err.Clear
'Vlookup to add business manager
Sheet1.Range("H" & i) = Application.VLookup(Sheet1.Range("G" & i), Sheet3.Range("A:B"), 2, False)
'Vlookup to add comments from 2 days ago to column AB
Sheet1.Range("AB" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 20, False)
'Vlookup to add comments from yesterday to column AC
Sheet1.Range("AC" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 21, False)
'Vlookup to add data from Focus List column
Sheet1.Range("AE" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 22, False)
'Vlookup to add data from % Chance column
Sheet1.Range("AF" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 23, False)
'Vlookup to add data from Allocation Status column
Sheet1.Range("AG" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 24, False)
'Vlookup to add data from New PO Date column
Sheet1.Range("AH" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 25, False)
'Vlookup to add data from 3 days ago to column AI
Sheet1.Range("AI" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 19, False)
'Vlookup to add data from 4 days ago to column AJ
Sheet1.Range("AJ" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 26, False)
'Vlookup to add data from 5 days ago to column AK
Sheet1.Range("AK" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 27, False)
'Vlookup to add data from 6 days ago to column AL
Sheet1.Range("AL" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 28, False)
'Vlookup to add data from 7 days ago to column AM
Sheet1.Range("AM" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 29, False)
'Vlookup to add data from 8 days ago to column AN
Sheet1.Range("AN" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 30, False)
'Vlookup to add data from 9 days ago to column AO
Sheet1.Range("AO" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 31, False)
'Vlookup to add data from 10 days ago to column AP
Sheet1.Range("AP" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AR"), 32, False)
'Vlookup to add data to VNX Models column AR from VNX Models sheet 5 using Item Number column
Sheet1.Range("AR" & i) = Application.VLookup(Sheet1.Range("W" & i), Sheet5.Range("A:B"), 2, False)
'Vlookup to add data to Upside X column B
Sheet1.Range("B" & i) = Application.VLookup(Sheet1.Range("J" & i), Sheet2.Range("J:AV"), 39, False)
'Vlookup to add Country to Country column AT based on Mapping Table sheet 4
Sheet1.Range("AT" & i) = Application.VLookup(Sheet1.Range("G" & i), Sheet4.Range("A:B"), 2, False)
'Vlookup to add Theater to Theater column AU based on Mapping Table sheet 4
Sheet1.Range("AU" & i) = Application.VLookup(Sheet1.Range("G" & i), Sheet4.Range("A:C"), 3, False)
If Err.Number = 0 Then
Else
End If
Next
'Adding Formula for Commit + X Column AS for Conf Call Analysis file
Range("AS2").Formula = "=IF(C2=""Commit"",""Commit+X"",IF(B2=""X"",""Commit+X"",""""))"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS" & Number_Rows)
'***********FORMATTING BELOW**************
'Format Revenue column K - no decimal, 1,000 seperator.
Columns("K:K").Select
Selection.NumberFormat = "#,##0"
'Format Forecast Close Date column P
Columns("P:P").Select
Selection.NumberFormat = "d/m/yyyy"
'Format Focus List column AE
Columns("AE:AE").Select
Selection.NumberFormat = "d/m/yyyy"
'Format % Chance column AF
Columns("AF:AF").Select
Selection.NumberFormat = "0%"
'Format cells with Dates as titles to dd-mm rather than long date
Range("AB1:AC1").Select
Selection.NumberFormat = "d-mmm"
Range("AI1:AP1").Select
Selection.NumberFormat = "d-mmm"
'Add Data Validation to Allocation Status column
Columns("AG:AG").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=_Allocation"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'Sorting by Description A-Z, Business Manager A-Z and Revenue $ Largest to Smallest
Sheet1.Sort.SortFields.Clear
Sheet1.Sort.SortFields.Add Key:=Range("C2:C" & Number_Rows _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Sheet1.Sort.SortFields.Add Key:=Range("H2:H" & Number_Rows _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Sheet1.Sort.SortFields.Add Key:=Range("K2:K" & Number_Rows _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Sheet1.Sort
.SetRange Range("A1:AU" & Number_Rows)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Hide Columns Quarter AA,Primary Partner OO, Reporting Product Type TT, Product Line VV, Opportunity Source Code QQ
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
Columns("O:O").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("V:V").Select
Selection.EntireColumn.Hidden = True
'Colours Today column AD yellow
Columns("AD:AD").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Colours Allocation Status column AG blue
Columns("AG:AG").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Bold Headings in row 1
Rows("1:1").Select
Selection.Font.Bold = True
'Adding Conditional Formatting Order Number column J, to highlight all duplicate values so that all oppts that have more than one row are red.
Columns("J:J").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'Adding last updated date
Sheet3.Range("J1") = Date
'Allows screen to refresh at the end
Application.ScreenUpdating = True
'Msgbox to advise when macro is complete.
MsgBox ("File is now updated.")
End Sub
我已经找到了一些解决方法,但是,这些方法会降低我们的工作效率,并且错误可能会在当天晚些时候再次出现。有人可以告诉我这个问题的可能原因是什么吗?
最佳答案
为了避免冗长的评论部分,我们昨晚继续聊天。结果比预期的要快。他提供了下面的 DefinedName block 。
尽管我看到了很多可能/没有贡献的事情,但在共享 View 中看起来最像是破裂的关系。它是一个共享工作簿,位于网络驱动器上,从外观上看,有 6 台不同的计算机都存储了一些个人 View 数据。语法看起来都不错,因此如果没有看到所有的sheet*.xml 文件,它可能位于<definedName>
之一中。 ,和<customWorkbookViews>
/<customSheetViews>
所有节点都基于 GUID 相关。由于这些 GUID 是由 excel 在运行时生成的,因此很容易失去控制。
在“审阅”选项卡>“共享工作簿”>“高级”选项卡中的“包含在个人 View 中”标题下关闭“过滤器设置”将删除全部.wvu
worksheet.xml 中的命名节点,以及 *View(s)
sheet*.xml 中的节点,并防止它们重新出现。这将解决该问题,或将候选项减少到一个用户定义的范围和一个系统定义的范围。最近的保存问题很可能导致某些文件部分没有更新,从而导致关系破裂。
缺点:只能保存一种 View 状态,因此打开文件时您将看到最后用户格式/过滤器的更改。
希望它真的能解决这个问题!如果没有,我们将进行另一轮。
UserBView MSDN (The strangely formatted Z_GUID part)
customSheetViewClass文档指出GUID“应对应于customWorkbookView”,其中customWorkbookView只需要一个包含全局唯一GUID的名称(我知道是多余的,但它在文档中) 。不满足“应”要求将导致出现“已删除记录:来自/xl/workbook.xml/part(工作簿)的命名范围”修复消息。
'EOQ TRACKER - MARCH 20TH V2.xlsm\workbook.xls - definedNames
<definedNames>
<definedName name="_Allocation">'6'!$A$2:$A$25</definedName>
<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'1'!$A:$AV</definedName>
<definedName name="Z_02EBD829_B457_4EED_8A23_48BD791D2A90_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A$1:$AU$5396</definedName>
<definedName name="Z_5038F31E_F522_4C27_8172_A37A2E0A913D_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A:$AV</definedName>
<definedName name="Z_6179D930_DC8C_44A7_9B7D_D78327247088_.wvu.Cols" localSheetId="0" hidden="1">'1'!$A:$A,'1'!$O:$O,'1'!$Q:$Q,'1'!$T:$T,'1'!$V:$V</definedName>
<definedName name="Z_6179D930_DC8C_44A7_9B7D_D78327247088_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A$1:$AU$5396</definedName>
<definedName name="Z_7736F281_6E25_4779_AF1F_AC145F2EB4E9_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A$1:$AU$5396</definedName>
<definedName name="Z_A2BCDFEA_821A_444D_B1F8_3659E07AD9AC_.wvu.Cols" localSheetId="0" hidden="1">'1'!$A:$A,'1'!$O:$O,'1'!$Q:$Q,'1'!$T:$T,'1'!$V:$V</definedName>
<definedName name="Z_A2BCDFEA_821A_444D_B1F8_3659E07AD9AC_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A:$AV</definedName>
<definedName name="Z_A2BCDFEA_821A_444D_B1F8_3659E07AD9AC_.wvu.Rows" localSheetId="0" hidden="1">'1'!:$1048576</definedName>
<definedName name="Z_AC1AB890_7D16_45DD_B4FB_13CB8F92D4C2_.wvu.Cols" localSheetId="0" hidden="1">'1'!$A:$A,'1'!$O:$O,'1'!$Q:$Q,'1'!$T:$T,'1'!$V:$V</definedName>
<definedName name="Z_AC1AB890_7D16_45DD_B4FB_13CB8F92D4C2_.wvu.FilterData" localSheetId="0" hidden="1">'1'!$A:$AV</definedName>
</definedNames>
关于vba - Microsoft Excel 错误 "Removed Records: Named range from/xl/workbook.xml part (Workbook)"的原因是什么,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29651955/
我正在使用 .remove() 方法删除一个 html 元素,同时对于这个元素,我有一个事件处理程序,但它没有被触发。为什么会这样呢?这是jsFiddle和代码:HTML Delete I'm goi
所以我尝试从另一篇文章中编写此代码: while(fscanf(orderFile," %49[^;];%d; %49[^\n]",fileName,&seconds,timeValue) == 3)
我正在阅读 Nicolai M.Josuttis 撰写的“The C++ STL. A Tutorial and References”一书,在专门介绍 STL 算法的一章中,作者陈述如下:如果你调用
是否有一种简单的机制来确定 DownloadManager remove() 何时完成,因为它看起来是部分异步的。该函数几乎立即返回下载表中已删除的条目计数,但实际的文件系统管理似乎被插入了某个后台线
我愿意: getActionBarToolbar().removeView(logoImage); getActionBarToolbar().addView(logoImage, lp); 我得到:
我有类(class)评论一对多关系。在类(class)表中有 id 和 title 列。在 Review 表中,有 id、comment 和 course_id,其中“course_id”作为指向类(
我在 stackoverflow 上阅读了不同的答案,了解如何销毁 wigdet/jQueryObject 并取消绑定(bind)其上的所有事件。 这就是我的想法。 $('选择器').remove()
我有一个由一个线程填充的 byte[] 列表,然后我有另一个线程正在从该列表中读取并通过网络发送项目。 每次我读取线程 2 中的项目时,我都想将其从内存中清除。但是因为我正在使用线程,如果我使用 .r
就算法而言,从连续数组中删除一组元素可以分两部分有效地完成。 将所有不删除的元素移到数组的前面。 将数组标记得更小。 这可以在 C++ 中使用 erase-remove 习惯用法来完成。 vector
我尝试删除包含在 map 中渲染的制造商的 View 。当我单击按钮时,事件 click .ver 被激活,但没有任何反应,并且我收到以下错误:Uncaught TypeError: undefine
场景: 使用 jQuery 2.0.1 构建的应用程序。 您的团队更喜欢原生 JavaScript。 选项有jQuery .remove()和 ChildNode.remove() . 您需要删除节点
最初我有一个像这样的删除功能: function ViewWorkflowDetail(btn, workflowId) { $("#workflowDetailPanel").remov
我正在编写 C++ 代码来解决 Leetcode 中的这个问题:https://leetcode.com/problems/remove-element/ Given an array nums an
根据太阳, "Iterator.remove is the only safe way to modify a collection during iteration; the behavior is
众所周知,从 std::vector 中完全删除所需项的一种好方法是 erase-remove idiom . 如以上链接中所述(截至本文发布日期),在代码中,erase-remove 习惯用法如下所
我在 HashSet 上调用 Iterator.remove() 时遇到问题。 我有一组带有时间戳的对象。在将新项目添加到集合之前,我会遍历集合,识别该数据对象的旧版本并将其删除(在添加新对象之前)。
这段代码: Collection col = new ArrayList(); col.add("a"); col.add("b"); col.add("c");
我试图通过在下面输入来卸载 conda 环境基础, conda env remove -n base 正如我所建议的那样,我尝试通过使用来停用基地 conda deactivate base 我再次尝
我已经对我的 IOS 应用程序进行了质量扫描分析。我收到以下警告: The binary has Runpath Search Path (@rpath) set. In certain cases
这个问题已经有答案了: Properly removing an Integer from a List (8 个回答) 已关闭 4 年前。 我是java新手。看起来很简单,但我不明白为什么会发生这种
我是一名优秀的程序员,十分优秀!