我的公式有效，但不引用With循环中正在处理的行

Question

使用Excel VBA中的UserForm，我似乎无法使公式部分引用代码中使用的当前行上的单元格。我不希望它只引用第2行的数据。第1行是标题行。

我该怎么做？

Private Sub cmdAccept_Click()
Dim lastrow As Long
Dim RowCount As Long
Dim fieldData As Worksheet
    lastrow = Range("A65536").End(xlUp).Row
RowCount = Worksheets("fieldData").Range("A1").CurrentRegion.Rows.Count
With Worksheets("fieldData").Range("A1")
    .Offset(RowCount, 0).Value = Me.txtBox_fieldData_dateDelivered.Value
    .Offset(RowCount, 1).Value = Me.comboBox_fieldData_fieldName.Value
    .Offset(RowCount, 2).Value = Me.txtBox_fieldData_acres.Value
    .Offset(RowCount, 3).Value = Me.comboBox_fieldData_crop.Value
    .Offset(RowCount, 4).Value = Me.txtBox_fieldData_product1.Value
    .Offset(RowCount, 5).Value = Me.txtBox_fieldData_product2.Value
    .Offset(RowCount, 6).Value = Me.txtBox_fieldData_product3.Value
    .Offset(RowCount, 7).Value = Me.txtBox_fieldData_product4.Value
    .Offset(RowCount, 8).Value = Me.txtBox_fieldData_product5.Value
    .Offset(RowCount, 9).Value = Me.txtBox_fieldData_product6.Value
    .Offset(RowCount, 10).Value = "=Range($E2:$J2)"
    .Offset(RowCount, 11).Value = "=SUM((11.06*$E2)+(11.7*$F2)+(11.04*$G2)+(10.9*$H2)+(10.28*$I2)+(9.5*$J2)"
    .Offset(RowCount, 12).Value = "=SUM(((11.06*$E2)*(32/100)/$C2)+((11.7*$F2)*(10/100)/$C2)+((11.04*$G2)*(12/100)/$C2)+((10.9*$H2)*(8/100)/$C2)+((10.28*$I2)*(7/100)/$C2)+((9.5*$J2)*(0/100)/$C2))"
    .Offset(RowCount, 13).Value = "=SUM(((11.06*$E2)*(0/100)/$C2)+((11.7*$F2)*(34/100)/$C2)+((11.04*$G2)*(0/100)/$C2)+((10.9*$H2)*(25/100)/$C2)+((10.28*$I2)*(24/100)/$C2)+((9.5*$J2)*(0/100)/$C2))"
    .Offset(RowCount, 14).Value = "=SUM(((11.06*$E2)*(0/100)/$C2)+((11.7*$F2)*(0/100)/$C2)+((11.04*$G2)*(0/100)/$C2)+((10.9*$H2)*(0/100)/$C2)+((10.28*$2I)*(0/100)/$C2)+((9.5*$J2)*(0/100)/$C2))"
    .Offset(RowCount, 15).Value = "=SUM(((11.06*$E2)*(0/100)/$C2)+((11.7*$F2)*(0/100)/$C2)+((11.04*$G2)*(26/100)/$C2)+((10.9*$H2)*(0/100)/$C2)+((10.28*$I2)*(0/100)/$C2)+((9.5*$J2)*(0/100)/$C2))"
End With
End Sub

用户将在UserForm中输入特定数据，UserForm将填充第1列至第10列，单击"接受数据"，然后运行公式，结果将放在从第K列至第p列的同一行中。完成后，用户将继续输入数据，直到按下"关闭"命令按钮。

Answer 1

让我知道这对您来说是如何工作的。

Private Sub cmdAccept_Click()
    Dim x As Long
    Dim fieldData As Worksheet
    With Worksheets("fieldData")
        x = .Range("A" & Rows.Count).End(xlUp).row + 1
        .Cells(x, 1).Value = Me.txtBox_fieldData_dateDelivered.Value
        .Cells(x, 2).Value = Me.comboBox_fieldData_fieldName.Value
        .Cells(x, 3).Value = Me.txtBox_fieldData_acres.Value
        .Cells(x, 4).Value = Me.comboBox_fieldData_crop.Value
        .Cells(x, 5).Value = Me.txtBox_fieldData_product1.Value
        .Cells(x, 6).Value = Me.txtBox_fieldData_product2.Value
        .Cells(x, 7).Value = Me.txtBox_fieldData_product3.Value
        .Cells(x, 8).Value = Me.txtBox_fieldData_product4.Value
        .Cells(x, 9).Value = Me.txtBox_fieldData_product5.Value
        .Cells(x, 10).Value = Me.txtBox_fieldData_product6.Value
        .Cells(x, 11).Value = "=Range($E2:$J2)"
        .Cells(x, 12).Value = "=SUM((11.06*$E2)+(11.7*$F2)+(11.04*$G" & x & ")+(10.9*$H" & x & ")+(10.28*$I2)+(9.5*$J2)"
        .Cells(x, 13).Value = "=SUM(((11.06*$E2)*(32/100)/$C" & x & ")+((11.7*$F2)*(10/100)/$C" & x & ")+((11.04*$G" & x & ")*(12/100)/$C" & x & ")+((10.9*$H" & x & ")*(8/100)/$C" & x & ")+((10.28*$I2)*(7/100)/$C" & x & ")+((9.5*$J2)*(0/100)/$C" & x & "))"
        .Cells(x, 14).Value = "=SUM(((11.06*$E2)*(0/100)/$C" & x & ")+((11.7*$F2)*(34/100)/$C" & x & ")+((11.04*$G" & x & ")*(0/100)/$C" & x & ")+((10.9*$H" & x & ")*(25/100)/$C" & x & ")+((10.28*$I2)*(24/100)/$C" & x & ")+((9.5*$J2)*(0/100)/$C" & x & "))"
        .Cells(x, 15).Value = "=SUM(((11.06*$E2)*(0/100)/$C" & x & ")+((11.7*$F2)*(0/100)/$C" & x & ")+((11.04*$G" & x & ")*(0/100)/$C" & x & ")+((10.9*$H" & x & ")*(0/100)/$C" & x & ")+((10.28*$2I)*(0/100)/$C" & x & ")+((9.5*$J2)*(0/100)/$C" & x & "))"
        .Cells(x, 16).Value = "=SUM(((11.06*$E2)*(0/100)/$C" & x & ")+((11.7*$F2)*(0/100)/$C" & x & ")+((11.04*$G" & x & ")*(26/100)/$C" & x & ")+((10.9*$H" & x & ")*(0/100)/$C" & x & ")+((10.28*$I2)*(0/100)/$C" & x & ")+((9.5*$J2)*(0/100)/$C" & x & "))"
    End With
End Sub

Answer 2

非常感谢Thomas，让我对其他编码方法大开眼界！！！这是我用来制作公式的代码。。。

Private Sub cmdAccept_Click()
Dim RowCount As Long
Dim fieldData As Worksheet
Set fieldData = Worksheets("fieldData")
RowCount = fieldData.Range("A1").CurrentRegion.Rows.Count + 1
With fieldData
    .Cells(RowCount, 1).Value = Me.txtBox_fieldData_dateDelivered.Value
    .Cells(RowCount, 2).Value = Me.comboBox_fieldData_fieldName.Value
    .Cells(RowCount, 3).Value = Me.txtBox_fieldData_acres.Value
    .Cells(RowCount, 4).Value = Me.comboBox_fieldData_crop.Value
    .Cells(RowCount, 5).Value = Me.txtBox_fieldData_product1.Value
    .Cells(RowCount, 6).Value = Me.txtBox_fieldData_product2.Value
    .Cells(RowCount, 7).Value = Me.txtBox_fieldData_product3.Value
    .Cells(RowCount, 8).Value = Me.txtBox_fieldData_product4.Value
    .Cells(RowCount, 9).Value = Me.txtBox_fieldData_product5.Value
    .Cells(RowCount, 10).Value = Me.txtBox_fieldData_product6.Value
    .Range("K" & RowCount - 1 & ":P" & RowCount).FillDown
End With

结束子