刚学VBA,我在工作簿1中批量循环打开一批工作簿,然后修改其中各个工作表中指定的单元格数据,这时我用了大量的activeworsheet代码例如
If ActiveWorkbook.Sheets("1").Range("D112").Value = ActiveWorkbook.Sheets("1").Range("D81").Value / 2 Then
ActiveWorkbook.Sheets("1").Range("D112").Value = ActiveWorkbook.Sheets("1").Range("D81").Value * 0.3 + 0.4
Else: ActiveWorkbook.Sheets("1").Range("d81").Interior.ColorIndex = color
ActiveWorkbook.Sheets("1").Range("T112").Value = "1"
End If
或简化为
With ActiveWorkbook.Sheets("1")
If .Range("D112") = .Range("D81") / 2 Then
.Range("D112") = .Range("D81") * 0.3 + 0.4
Else: .Range("D81").Interior.ColorIndex = Color
.Range("T112") = "1"
End If
End With
With ActiveWorkbook.Sheets("2")
For I = 81 To 84
.Cells(I, "O") = "=D" & I & "*I" & I
Next
End With
你可以写成这样
With ActiveWorkbook.Sheets("1")
If .Range("D112").Value = .Range("D81").Value / 2 Then
.Range("D112").Value = .Range("D81").Value * 0.3 + 0.4
Else: .Range("d81").Interior.ColorIndex = Color
.Range("T112").Value = "1"
End If
End With
With ActiveWorkbook.Sheets("2")
.Range("O83").Value = "=D83*I83"
.Range("O84").Value = "=D84*I84"
.Range("O81").Value = "=D81*I81"
.Range("O82").Value = "=D82*I82"
End With
或简化为
With ActiveWorkbook.Sheets("1")
If .Range("D112") = .Range("D81") / 2 Then
.Range("D112") = .Range("D81") * 0.3 + 0.4
Else: .Range("D81").Interior.ColorIndex = Color
.Range("T112") = "1"
End If
End With
With ActiveWorkbook.Sheets("2")
For I = 81 To 84
.Cells(I, "O") = "=D" & I & "*I" & I
Next
End With