excel - 修剪整个工作表中的单元格,溢出错误
问题描述
我的代码加载了 2 个工作簿,并将它们复制到主工作簿中。但是我得到了一个
溢出错误
当我试图修剪粘贴表中的所有单元格时(也删除空格)。
有谁知道为什么在整张纸上修剪多余的空白时会发生这种溢出错误?具体来说,我在这部分得到了错误Target = Target.Value
。
Sub Load()
LoadDailyWorkbook
LoadLastWeeksWorkbook
End Sub
Sub LoadDailyWorkbook()
Const A1BJ200 As String = "A1:BJ200"
Const A1L3 As String = "A1:L3"
Dim masterWB As Workbook
Dim dailyWB As Workbook
'Set Current Workbook as Master
Set masterWB = Application.ThisWorkbook
'Set some Workbook as the one you are copying from
Set dailyWB = getWorkbook(Sheets("Control Manager").Range("O2"))
If Not dailyWB Is Nothing Then
With dailyWB
'Copy the Range from dailyWB and Paste it into the MasterWB
.Worksheets("Summary1").Range(A1BJ200).Copy masterWB.Worksheets("Summary").Range("A1")
TrimRange masterWB.Worksheets("Summary").Range(A1BJ200)
'repeat for next Sheet
.Worksheets("risk1").Range(A1BJ200).Copy masterWB.Worksheets("risk").Range("A1")
TrimRange masterWB.Worksheets("risk").Range(A1BJ200)
'repeat for CS sheet
.Worksheets("CS today").Range(A1L3).Copy masterWB.Worksheets("CS").Range("A1").Rows("1:1")
TrimRange masterWB.Worksheets("CS").Range(A1L3)
.Close SaveChanges:=False
End With
End If
End Sub
Sub LoadLastWeeksWorkbook()
Const A1BJ200 As String = "A1:BJ200"
Dim masterWB As Workbook
Dim lastweekWB As Workbook
'Set Current Workbook as Master
Set masterWB = Application.ThisWorkbook
''''''''''''Get Last Week Data''''''''''''''''''''''
Set lastweekWB = getWorkbook(Workbooks.Open(Sheets("Control Manager").Range("O3")))
If Not lastweekWB Is Nothing Then
With lastweekWB
'repeat for next risk Sheet
.Worksheets("risk2").Range(A1BJ200).Copy masterWB.Worksheets("risk_lastweek").Range("A1")
TrimRange masterWB.Worksheets("risk_lastweek").Range(A1BJ200)
TrimRange masterWB.Columns("A:BB")
.Close SaveChanges:=False
End With
End If
End Sub
Function getWorkbook(FullName As String) As Workbook
If Len(Dir(FullName)) = 0 Then
MsgBox FullName & " not found found", vbCritical, "File Not Found"
Else
Set getWorkbook = Workbooks.Open(FullName)
End If
End Function
Sub TrimRange(Target As Range)
Dim results As Variant
Set Target = Intersect(Target.Parent.UsedRange, Target)
If Target Is Nothing Then
Exit Sub
ElseIf Target.Count = 1 Then
Target.Value = Trim(Target.Value)
Exit Sub
Else
Target = Target.Value
Dim r As Long, c As Long
For r = 1 To UBound(results)
For c = 1 To UBound(results, 2)
results(r, c) = Trim(results(r, c))
Next
Next
Target.Value = results
End If
Target.Columns.EntireColumn.AutoFit
End Sub
解决方案
Sub TrimRange(Target As Range)
Dim results As Variant
results
然而,你在使用它之前没有设置。
For r = 1 To UBound(results)
所以你在呼唤UBound
一些不存在的东西。
此外,当我将公式更改为值时,我使用Target.Value = Target.Value
了Target = Target.Value
. 我知道这.Value
通常是默认值,但我从不相信隐含的东西一直都能正常工作。
推荐阅读
- r - 在看门人 tabyl 中使用字符串!!!顶层
- javascript - 将响应 Request.post 令牌保存在变量中
- python - 导入多个 CSV 文件并要求用户输入选择文件
- opencv - 尝试使用 pip 安装 opencv-python 不断引发错误。该怎么办?
- shell - 通过 PM2 将环境变量传递给 NextJS
- java - 在 JHipster 微服务应用程序中构建 UI 更改
- php - 从json响应中获取特定值不起作用
- react-native - 更新类型阴影节点中的属性“fontSize”时出错:RCTText
- typescript - 扩展不止一个类
- r - 将当前为文本文件的单个单元格计数矩阵转换为数据框