目的:对多个文件做同样的修改。
错误:(运行时错误“13”:类型不匹配)在行 >Enter_Formulas wb
Option Explicit
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = "C:\Users\E\Desktop\macro2\"
Filename = Dir(Pathname)
Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
Enter_Formulas wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
End Sub
Sub Enter_Formulas(wb As Workbooks)
With wb
.Worksheets(1).Range("G19", Range("F19").End(xlDown).Offset(0, 1)).FormulaR1C1 = "=IF(R[0]C[-2]<0,R[-1]C[-5])"
.Worksheets(1).Range("H19", Range("F19").End(xlDown).Offset(0, 2)).FormulaR1C1 = "=IF(R[0]C[-3]<0,R[-1]C[-5])"
.Worksheets(1).Range("I19", Range("F19").End(xlDown).Offset(0, 3)).FormulaR1C1 = "=IF(R[0]C[-4]<0,R[-1]C[-5])"
.Worksheets(1).Range("H7").FormulaR1C1 = "=ROUND(R[10]C,0)=ROUND(RC[-6],0)"
End With
End Sub
注意:如果主体 atEnter_Formulas()
是:.Worksheets(1).Range("A1").Value = "pointless"
您不能依赖于父工作表引用的默认活动工作表。
.Worksheets(1).Range("G19", Range("F19").End(xlDown).Offset(0, 1)).FormulaR1C1 = "=IF(R[0]C[-2]<0,R[-1]C[-5])"
如果 Worksheets(1) 是活动工作表,则 Range("F19") 仅属于 Worksheets(1)。完全限定父工作表很容易。
Sub Enter_Formulas(wb As Workbooks)
With wb.Worksheets(1)
.Range(.cells(19, "G"), .cells(19, "F").End(xlDown).Offset(0, 1)).FormulaR1C1 = "=IF(R[0]C[-2]<0,R[-1]C[-5])"
.Range(.cells(19, "H"), .cells(19, "F").End(xlDown).Offset(0, 2)).FormulaR1C1 = "=IF(R[0]C[-3]<0,R[-1]C[-5])"
.Range(.cells(19, "I"), .cells(19, "F").End(xlDown).Offset(0, 3)).FormulaR1C1 = "=IF(R[0]C[-4]<0,R[-1]C[-5])"
.Range("H7").FormulaR1C1 = "=ROUND(R[10]C,0)=ROUND(RC[-6],0)"
End With
End Sub
.Range("F19")
现在注意,不是Range("F19")
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句