+ Reply to Thread
Results 1 to 2 of 2

How to enter a formula into a cell after a macro runs

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    How to enter a formula into a cell after a macro runs

    Ok so I have this sheet that when a command button is pressed by the user it takes some cell values and paste them into another sheet. It then clears all the data in cells that are specified and then hides rows for the user to start over. There are some cells that have formulas in them that could be over written by the user if need be. These cells are C132, C133 and C134. Below are the formulas in these cells.
    C132 formula is =IF(ISBLANK(C123),"",$Q$109+$Q$94+$Q$79+$Q$64+$Q$49+$Q$34+$Q$19+$Q$4)
    C133 formula is =IF(ISBLANK(C123),"",IF(COUNT($W$109,$W$94,$W$79,$W$64,$W$49,$W$34,$W$19,$W$4)>0,SUM($W$4,$W$19,$W$34,$W$49,$W$64,$W$79,$W$94,$W$109),""))
    C134 formula is =IF(AND(C125="",C123=""),"",IFERROR(SUM($V:$V)/(COUNT($V:$V)-COUNTIF($V:$V,0)),0))

    What I want to happen is after the command button is pressed, I'd like to put these formulas back into the cells where they belong. If the user has changed the cells and manually put in a value.

    The code for the command button is this.
    Private Sub CommandButton2_Click()
        ActiveSheet.Unprotect
        Application.ScreenUpdating = False
        
        Dim rName  As Range
        Dim dName As Range
        Dim foundName As Range
        Dim found2Name As Range
        Dim lColumn As Long
        
        For Each rName In Sheets("Moulder 1").Range("C123:H123")
            If rName <> "" Then
                Set foundName = Sheets("Employees").Range("A:A").Find(rName, LookIn:=xlValues, lookat:=xlWhole)
                If Not foundName Is Nothing Then
                    lColumn = Application.WorksheetFunction.Weekday(Sheets("Moulder 1").Range("C1").value) + 3
                    If lColumn = 3 Then
                        lColumn = 4
                    End If
                    Sheets("Employees").Cells(foundName.Row, lColumn) = Val(Sheets("Employees").Cells(foundName.Row, lColumn)) + rName.Offset(5, 0)
                End If
            End If
        Next rName
        For Each dName In Sheets("Moulder 1").Range("O123,Q123")
            If dName <> "" Then
                Set found2Name = Sheets("Employees").Range("A:A").Find(dName, LookIn:=xlValues, lookat:=xlWhole)
                If Not found2Name Is Nothing Then
                    lColumn = Application.WorksheetFunction.Weekday(Sheets("Moulder 1").Range("C1").value) + 3
                    If lColumn = 3 Then
                        lColumn = 4
                    End If
                    Sheets("Employees").Cells(found2Name.Row, lColumn) = Val(Sheets("Employees").Cells(found2Name.Row, lColumn)) + dName.Offset(1, 0)
                End If
            End If
        Next dName
        Range("Q124,O124,Q123,O123").ClearContents
        Range("C123:H123,Q109,O109,G109,F109,B109,B94,F94,G94,O94,Q94,Q79,O79,G79,F79,B79").ClearContents
        Range("Q64,O64,G64,F64,B64,Q49,O49,G49,F49,B49,B64,Q34,O34,G34,F34,B34,Q19,O19,G19,F19,B19").ClearContents
        Range("Q4,O4,G4,F4,B4").ClearContents
        
        Rows("7:119").Hidden = True
        Range("B4").Select
        ActiveSheet.Protect
        Application.ScreenUpdating = True
        
    End Sub
    Thanks in advance for everything
    excel 2010

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to enter a formula into a cell after a macro runs

    You should record yourself putting these formulae into the cells

    Stop the Macro recorder and then add the code to your macro

    This is what I recorded I obviously put the formulae in any old cell


    
    
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        Range("E9").Select
        ActiveSheet.Paste
        Selection.FormulaR1C1 = _
            "=IF(ISBLANK(R[114]C[-2]),"""",R109C17+R94C17+R79C17+R64C17+R49C17+R34C17+R19C17+R4C17)"
        ActiveCell.FormulaR1C1 = _
            "=IF(ISBLANK(R[114]C[-2]),"""",R109C17+R94C17+R79C17+R64C17+R49C17+R34C17+R19C17+R4C17)"
        Range("E16").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(ISBLANK(R[107]C[-2]),"""",IF(COUNT(R109C23,R94C23,R79C23,R64C23,R49C23,R34C23,R19C23,R4C23)>0,SUM(R4C23,R19C23,R34C23,R49C23,R64C23,R79C23,R94C23,R109C23),""""))" & Chr(10) & "                                                                                                                                                                                                          " & _
            "                                              R[118]C[-2] formula is =IF(AND(R[109]C[-2]="""",R[107]C[-2]=""""),"""",IFERROR(SUM(C22)/(COUNT(C22)-COUNTIF(C22,0)),0))"
        Range("E17").Select
    End Sub

    I then edit the above code

    [CODE]

    I then edit the above code

    
    Sub Macro1()
        Range("C133").FormulaR1C1 = _
            "=IF(ISBLANK(R[114]C[-2]),"""",R109C17+R94C17+R79C17+R64C17+R49C17+R34C17+R19C17+R4C17)"
    
        Range(" C134").FormulaR1C1 = _
            "=IF(ISBLANK(R[107]C[-2]),"""",IF(COUNT(R109C23,R94C23,R79C23,R64C23,R49C23,R34C23,R19C23,R4C23)>0,SUM(R4C23,R19C23,R34C23,R49C23,R64C23,R79C23,R94C23,R109C23),""""))" & Chr(10) & "                                                                                                                                                                                                          " & _
            "                                              R[118]C[-2] formula is =IF(AND(R[109]C[-2]="""",R[107]C[-2]=""""),"""",IFERROR(SUM(C22)/(COUNT(C22)-COUNTIF(C22,0)),0))"
    End Sub
    Last edited by mehmetcik; 11-11-2015 at 02:21 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Trouble with sub that runs on pressing enter after modifying cell's content
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-09-2014, 04:49 PM
  2. Macro won't enter complex formula in selected cell.
    By Fungijus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2013, 09:10 AM
  3. Unlocking Cell after Macro Runs
    By brittm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2009, 12:37 PM
  4. Don't select a cell after macro runs ???
    By richardeallen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2008, 11:16 AM
  5. Change cell value and macro runs
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-23-2007, 04:18 AM
  6. Cell value change runs a macro?
    By Steach91 in forum Excel General
    Replies: 6
    Last Post: 06-18-2006, 03:20 PM
  7. macro runs if I Clear a cell
    By Nimzow in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-20-2005, 11:06 AM
  8. [SOLVED] Can you enter a formula in a cell to run a macro?
    By Nevaeh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2005, 08:06 PM

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