|
楼主 |
发表于 2022-7-21 11:20
|
显示全部楼层
可以完美实现,感谢,最后我改了下,
Option Explicit
Sub 批量修改表格数据()
On Error Resume Next '// 发生错误,自动执行下一句,就是忽略错误
Application.ScreenUpdating = False '//关闭屏幕刷新
Application.DisplayAlerts = False '//关闭系统提示
Application.EnableEvents = False '//禁止触发其他事件
Application.StatusBar = False '关闭系统状态条
Dim F, I%, K%, R%, Tim, Ar, Str$
Dim Wb As Workbook
Dim Sh As Worksheet
Tim = Timer
ChDir ThisWorkbook.Path
F = Application.GetOpenFilename("EXCEL文件,*.xls*", MultiSelect:=True)
For K = 1 To UBound(F)
Set Wb = GetObject(F(K))
For Each Sh In Wb.Worksheets
If Sh.Name = "表三甲" Then
With Sh
.UsedRange.Interior.ColorIndex = xlNone
R = .UsedRange.Rows.Count
Ar = .Range("A7:M" & R)
For R = 1 To UBound(Ar)
If Ar(R, 5) <> Ar(R, 12) Then
Ar(R, 13) = Ar(R, 12) - Ar(R, 5)
.Cells(R + 6, 1).Resize(1, 13).Interior.ColorIndex = 3
Else
Ar(R, 13) = ""
End If
Next
.Range("A7:M" & R) = Ar
End With
End If
Next Sh
Wb.Windows(1).Visible = True
Wb.Close True
Set Wb = Nothing
Next K
Sheets(1).Select
MsgBox Format(Timer - Tim, "0.00")
Application.StatusBar = True '恢复系统状态条
Application.EnableEvents = True '// 恢复触发其他事件
Application.ScreenUpdating = True '//恢复屏幕刷新
Application.DisplayAlerts = True '//恢复系统提示
End Sub
|
|