|
不清楚你的公式是要做什么,具体你再根据你的需要改公式。但最好不要使用数组公式
代码如下:
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
j = Sheet1.UsedRange.Rows.Count
For i = 1 To j
If Cells(i, 1) = "" Or Cells(i, 2) = "" Then GoTo 100
If Cells(i, 3).Value = "" Then
Cells(i, 4).Formula = _ "=IF(SUM(--ISNUMBER(RC[-3]:RC[-2])),AVERAGE(IF(ISNUMBER(RC[-3]:RC[-2]),RC[-3]:RC[-2],RIGHT(RC[-3]:RC[-2],LEN(RC[-3]:RC[-2])-1)/2)),RC[-3])"
Else
Cells(i, 4).Formula = _ "=IF(SUM(--ISNUMBER(RC[-3]:RC[-1])),AVERAGE(IF(ISNUMBER(RC[-3]:RC[-1]),RC[-3]:RC[-1],RIGHT(RC[-3]:RC[-1],LEN(RC[-3]:RC[-1])-1)/2)),RC[-2])"
End If
100:
Next
End Sub
|
|