+ Reply to Thread
Results 1 to 5 of 5

Insert Formula if cell is changed from blank

  1. #1
    Registered User
    Join Date
    09-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Insert Formula if cell is changed from blank

    I'm trying to auto-insert a formula in column E if the user starts inputting data into one of several cells (in the corresponding row)

    Below is the macro I'm using but it only seems to kick in if I edit cells in column C. Any idea why it's not working for the other 3 criteria?

    Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Not Intersect(Range(Target.Address), Range("A:E")) _
    Is Nothing And Target.Row <> 1 Then

    Dim r As Long
    r = Target.Row

    If Cells(r, "A").Value <> "" Or _
    Cells(r, "B").Value <> "" Or _
    Cells(r, "C").Value <> "" Or _
    Cells(r, "D").Value <> "" Then

    ' the below adds in the formula in column E

    Cells(r, "E").FormulaR1C1 = "=IF(IF(RC[-1]="",RC[-2],RC[-1])=0,"",IF(RC[-1]="",RC[-2],RC[-1]))"

    End If
    End If

    Application.EnableEvents = True

    End Sub
    Last edited by buffalotom99; 09-24-2009 at 07:21 AM.

  2. #2
    Registered User
    Join Date
    09-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Insert Formula if cell is changed from blank

    Any takers?

  3. #3
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Insert Formula if cell is changed from blank

    hello buffalotom99

    1st up please use code tags.

    Here is a simpler way of getting that formular into the cell.
    I havent had much to do with formulars, It puts a syntax error when trying to use it from VBA so i just put in """FORMULAR:"" you will need to take a look at that/

    Please Login or Register  to view this content.
    hope this helps

    edit a liitle cleaner code

    Please Login or Register  to view this content.
    Last edited by D_Rennie; 09-24-2009 at 07:33 AM.

  4. #4
    Registered User
    Join Date
    09-24-2009
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Insert Formula if cell is changed from blank

    Sorry, first post.

    It still only seems to be triggering if I put a value in column C.... I don't know why.. :-(

  5. #5
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Insert Formula if cell is changed from blank

    I dont know.
    Ill take a better look if you want to post a workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1