Add formulas using VB in Excel 2016

[REV AJR]

Hi

I have created a form in VB to enter data into a table in excel, part of the macro adds formulas to the relevant cells, it almost works but I cannot get the equation to pickup the row reference for that cell.


Any help would be appreciated

Private Sub cmdenter_Click()
'Copy input values to sheet.
    Dim eRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data Sheet")
    eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(eRow, 1).value = txtLiftnumber.value
        .Cells(eRow, 2).value = txtSystemnumber.value
        .Cells(eRow, 3).value = txtArrivaldate.value
        .Cells(eRow, 4).value = cmbRefernce.value
        .Cells(eRow, 5).value = txtPackagenumber.value
        .Cells(eRow, 7).Formula = "=IF(F1299="""",0,IF(LEFT(D1299,1)=""R"",(F1299-C1299),IF(((F1299-C1299)-7)<0,1,(F1299-C1299)-7)))"
        .Cells(eRow, 8).Formula = "=G1298*0.35"
        .Cells(eRow, 9).value = "=IF(A1298="""",0,IF(F1298="""",(INT($B$1-C1298)),0))"
        .Cells(eRow, 10).Formula = "=I1298*0.35"
        .Cells(eRow, 11).value = txtComments.value
    End With
    'Clear input controls.
    Me.txtLiftnumber.value = ""
    Me.txtSystemnumber.value = ""
    Me.txtArrivaldate.value = ""
    Me.cmbRefernce.value = ""
    Me.txtPackagenumber.value = ""
    Me.txtComments.value = ""

 

[Roodey]

Dim eRowNext As Long
eRowNext = eRow + 1

Use this variable for your IF formula for column 7.

Example for 1 formula:
.Cells(eRow, 8).Formula = "=G" & eRow & "*0.35"

 

[REV AJR]

Spot on thank you.