|
本帖最后由 新会甜橙 于 2012-11-30 23:57 编辑
核对不了工作表的名称
同一文件夹内数据工作薄退信工作表的B列和明细工作薄的各工作表名称核对,退信工作表D列和明细工作薄各工作表的D列核对,相符时将退信工作表F、G单元格填入到明细工作薄同名工作表B、A单元格。(80多个表)
本帖最后由 hwc2ycy 于 2012-12-1 10:19 编辑
- Sub 替换记录()
- 'Dim app As Excel.Application
- 'Set app = Excel.Application
- Dim dworkbook$
- dworkbook = "\明细.xls"
- '检测当前文件夹是否存在明细.xls
- dworkbook = Dir(ThisWorkbook.Path & dworkbook, vbNormal)
- If Len(dworkbook) = 0 Then MsgBox "当前文件夹下无 " & dworkbook: Exit Sub
-
- Dim wb As Workbook
- '读取数据
- Dim arr, irow&
- irow = Range("b" & Rows.Count).End(xlUp).Row()
- If irow = 1 Then Exit Sub
- arr = Range("a1").CurrentRegion
-
- '安全设定
- Dim secAutomation As MsoAutomationSecurity
- secAutomation = Application.AutomationSecurity
- Application.AutomationSecurity = msoAutomationSecurityForceDisable
-
- '关闭屏幕刷新
- Application.ScreenUpdating = False
- 'set wb= workbooks.Open thisworkbo
- On Error Resume Next
- Set wb = Workbooks(dworkbook)
- If Err.Number <> 0 Then
-
- Set wb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & dworkbook)
- If Err.Number <> 0 Then
- MsgBox dworkbook & "打开失败"
- Err.Clear
- Exit Sub
- End If
- End If
- wb.Activate
-
- Dim shtname$
- For i = LBound(arr) + 1 To UBound(arr)
- 'On Error Resume Next
- shtname = Right(arr(i, 2), Len(arr(i, 2)) - 2)
- With Worksheets(shtname)
- If Err.Number <> 0 Then
- MsgBox "读取 " & arr(i, 2) & " 工作表出错"
- Err.Clear
- Else
- irow = .Range("e:e").Find(arr(i, 5), LookIn:=xlValues, LookAt:=xlWhole).Row
- If Err.Number <> 0 Then
- MsgBox arr(i, 5) & " 在 工作表 " & arr(i, 2) & " 中无匹配记录"
- Err.Clear
- Else
- .Cells(irow, "b") = arr(i, 6)
- .Cells(irow, "a") = arr(i, 7)
- End If
- End If
- End With
- Next
-
- Application.AutomationSecurity = secAutomation
- Application.ScreenUpdating = True
-
- wb.Save
- End Sub
复制代码根据楼主改过的数据写的。对了,数据的D列与明细的D列,因为逗号加入的位置不同,所以匹配的时候用的E列做比对的。
有些数据更新不成功。
另外,分行的名字一定要写准确了,你把江门都省去,这还好点,但后面有个营业部的最好还是不要省去,该一一对应的就一一对应,虽然用代码也能解决,但是如果数据在录入的时候做规范了,对于写代码是非常有益的。
|
|