我目前正在开发一个vba,用于在同一工作表中的列之间进行乘除。
下面是我的代码
Sub test1()
Dim i As Long
For i = 3 To 100
Cells(i, 12) = (Cells(i, 8)) / (Cells(i, 6)) / 10 * 0.85
Cells(i, 13) = (Cells(i, 9)) / (Cells(i, 6)) / 10 * 0.85
Next i
End Sub
- 但我在代码的第4行收到一个
Overflow
错误。有什么建议吗 - 此外,我希望该程序能为我当前正在处理的当前工作簿中的所有活动工作表运行
提前感谢
您将得到一个错误,因为您的一个分割单元格的为空
实际上,您正在将此公式放入L3:M100
=IF($F3<>"",(H3/$F3)/10*0.85,"")
这可以在以下的所有片材中进行
(您可以录制一个宏来查看此公式在VBA中的外观)
Sub ReCut()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.Range("L3:M100")
.FormulaR1C1 = "=IF(RC6<>"""",(RC[-4]/RC6)/10*0.85,"""")"
.Value = .Value
End With
Next
End Sub
或者:
Sub M_snb()
[L3:M100] = [index(if(F3:F100="","",H3:I100/F3:F100/8.5),)]
End Sub
Try this
Sub test1()
Dim i, j As Integer
Dim Ws As Worksheet
For j = 1 To ThisWorkbook.Sheets.Count
Set Ws = ThisWorkbook.Sheets(j)
For i = 3 To 100
Ws.Cells(i, 12) = (Ws.Cells(i, 8)) / (Ws.Cells(i, 6)) / 10 * 0.85
Ws.Cells(i, 13) = (Ws.Cells(i, 9)) / (Ws.Cells(i, 6)) / 10 * 0.85
Next i
Next
End Sub