PDA

View Full Version : Where and how to enter an Excel formula



sheldon
09-02-2008, 01:12 AM
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

xld
09-02-2008, 02:13 AM
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
09-02-2008, 05:45 AM
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
09-03-2008, 06:17 AM
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

xld
09-03-2008, 06:29 AM
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
09-03-2008, 06:49 AM
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

xld
09-03-2008, 06:54 AM
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
09-04-2008, 12:58 AM
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

xld
09-04-2008, 01:05 AM
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
09-04-2008, 01:21 AM
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

xld
09-04-2008, 01:46 AM
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