Where and how to enter an Excel formula

[sheldon]

Hi,

Would be very gratful if you could help me with this problem.

I am trying to enter an Excel formula I created into VB. I have taken over ownership of a previously created document and I am trying to understand and learn, but struggling.

The Excel formula is: =IF(E14<>"","Returned",IF(AND(TODAY()>=C14+8,E14=""),"Require Chasing","No Action Required"))

I am wanting the formula in the new row every time a new record is entered.

Hope this is enough information.

Thanks

[Bob Phillips]

I have based this on changes to column A defines a new row, but this can be altered

Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "A:A"     '<== change to suit
    Const ADD_FORMULA = "=IF(RC5<>"""",""Returned"",IF(AND(TODAY()>=RC3+8,RC5=""""),""Require Chasing"",""No Action Required""))"
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> "" Then
                Me.Cells(.Row, "D").FormulaR1C1 = ADD_FORMULA
            End If
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.

[mdmackillop]

While it may not aid understanding so much, the simplest way to deal with complex formula is to remove the inital "=" sign and then record a macro inserting it. In this case I get

 
Sub Macro1()
    ' Macro1 Macro
    ActiveCell.FormulaR1C1 = _
    "=IF(R[5]C[-3]<>"""",""Returned"",IF(AND(TODAY()>=R[5]C[-5]+8,R[5]C[-3]=""""),""Require Chasing"",""No Action Required""))"
    Range("H10").Select
End Sub

You can then see how the references etc. are created, and modify them to suit.

[sheldon]

Thanks for the help.

I really like the idea of having a macro that simply inserts, the formula into the Excel cell when the new row of data is entered. But i am struggling to do this.

I'm guessing that i would need to add this into the "Add_Record" button. How would i do this?

Thanks

[Bob Phillips]

If you are referring to my code, you just install it as diected, and it will trigger when you change a value in column A.

[sheldon]

Thanks for the help. I have inserted the formula, but I am getting #NAME? displayed in the cell, where i would like the text displayed.

Is it possible for the formula to change everytime it is entered?
e.g. for the forumla to look at cell E1 in the first entry, but in the second entery of the formula it looks at E2 as the Excel sheet is filled with new records?

Thanks again for your help

[Bob Phillips]

It already does that, it refers to the row it is in.

I am at a loss at to what causes the #NAME. Can you post your workbook?

[sheldon]

Sorry I am not able to post my worksheet.

I have change details within the code you gave me to...

Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Const ADD_FORMULA = "=IF(M262<>"""",""Returned"",IF(AND(TODAY()>=K262+8,M262=""""),""Require Chasing"",""No Action Required""))"
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> "" Then
                Me.Cells(.Row, "N").FormulaR1C1 = ADD_FORMULA
            End If
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

is this correct, as column N is where I want the formula to be entered, K is a date sent and M is date received?

Thanks

[Bob Phillips]

An error in my code

Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Const ADD_FORMULA = "=IF(M262<>"""",""Returned"",IF(AND(TODAY()>=K262+8,M262=""""),""Require Chasing"",""No Action Required""))"
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> "" Then
                Me.Cells(.Row, "N").Formula = ADD_FORMULA
            End If
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub

[sheldon]

Thanks for the reply. This code works, but everytime it is entered into the worksheet it looks at M262 and K262. Is there a way to get it to look at the row that has just been entered, so if the next entry was on row 263 the formula would look at M263 and K263?

Thanks

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
    Const WS_RANGE As String = "A:A" '<== change to suit
    Const ADD_FORMULA = "=IF(M<rownum><>"""",""Returned"",IF(AND(TODAY()>=K<rownum>+8,M<rownum>=""""),""Require Chasing"",""No Action Required""))"
    On Error GoTo ws_exit
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            If .Value <> "" Then
                Me.Cells(.Row, "N").Formula = Replace(ADD_FORMULA, "<rownum>", .Row)
            End If
        End With
    End If
    ws_exit:
    Application.EnableEvents = True
End Sub