下一行的Excel VBA公式

Question

所以我有一个问题，我有一张excel表，数据被输入到UserForm中，然后在输入数据后进入下一个。

我的问题是我需要把这个公式放在A2列中

=IF(B2="","",IF(ISBLANK(I2()),"OPEN","CLOSED"))

并转到下一行，例如下一行A3输入

=IF(B3="","",IF(ISBLANK(I3()),"OPEN","CLOSED"))

等等，如果有意义的话。

提前感谢。

编辑：下面是将数据输入下一行的代码

'Submit MR and fill next available row in MR workbook
Private Sub buttonSend_Click()
Dim lRow As Long
Dim ws As Worksheet
Dim lValue As Date
Set ws = Worksheets("Maintance Report")
lRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
'Submit data
With ws
.Cells(lRow, 3).Value = Me.textName.Value
.Cells(lRow, 4).Value = Me.comboDepartment.Value
.Cells(lRow, 5).Value = Me.comboPriority.Value
.Cells(lRow, 6).Value = Me.textDescription.Value
.Cells(lRow, 2).Value = Now
End Sub

Answer 1

您可以这样做：

With .Range("A3")
.Formula = Replace("=IF(B#="""","""",IF(ISBLANK(I#()),""OPEN"",""CLOSED""))", _
"#", .Row)
End With