Consulting

Results 1 to 11 of 11

Thread: Where and how to enter an Excel formula

  1. #1
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location

    Where and how to enter an Excel formula

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location
    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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location
    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

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location
    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

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location
    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

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •