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

Bob Phillips

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

Bob Phillips

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

Bob Phillips

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

Bob Phillips

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

Bob Phillips

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.