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

[vba]


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
[/vba]

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
[VBA]
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

[/VBA]
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=""""),""Requir e 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

[vba]

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=""""),""Requir e 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
[/vba]

[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]

[vba]

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
[/vba]