|
有多个Excel表,希望把这个模具型号的产品换个材料,名称和型号是判断条件,只改变材料,如把aab-01的锁定轴的材料PP换成ABS,并能同时批量修改其他表。
- Private Sub CommandButton1_Click()
- Dim MyPath$, MyName$, Sh As Workbook
- Dim i&
- On Error Resume Next
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- MyPath = ThisWorkbook.Path & ""
- MyName = Dir(MyPath & "*.*")
- r = 0 '记录修改个数,所以起始为0
- Do While MyName <> "" '当文件名不为空时进行以下操作
- If MyName <> ThisWorkbook.Name Then '跳过当前工作表
- Set Sh = GetObject(MyPath & MyName) '打开工作簿
- With Sh.ActiveSheet '引用打开的工作簿的默认工作表
- For i = 6 To 25 '左边区域的数据是从第6行开始到25行结束,所以从第6行开始往下循环
- If .Cells(i, 2) = "锁定轴" And .Cells(i, 3) = "ABB-01" And .Cells(i, 4) <> "ABS" Then '判断是否符合条件,符合就进行修正
- r = r + 1
- .Cells(i, 4) = "ABS"
- End If
- Next
- For i = 3 To 20 '右边边区域的数据是从第3行开始到20行结束,所以从第3行开始往下循环
- If .Cells(i, 16) = "锁定轴" And .Cells(i, 17) = "ABB-01" And .Cells(i, 18) <> "ABS" Then '判断是否符合条件,符合就进行修正
- r = r + 1
- .Cells(i, 18) = "ABS"
- End If
- Next
- End With
- Windows(MyName).Visible = True '关闭隐藏窗口
- Workbooks(MyName).Close True '关闭工作簿
- End If
- MyName = Dir
- Loop
- MsgBox "总计修正" & r & "处数据" '消息框提示修正了几个
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|