Posts by argot65

    I have the below code that works great but only works when the event is changed. Once the event is triggered and the CALL macro is launched I can not get it to work again until I get it to fail. Is there a way to reset the event enabler to recalculate each time the code is launched.




    Code
    [/SIZE]Private Sub Worksheet_Calculate()        Dim FormulaRange As Range    Dim NotSentMsg As String    Dim MyMsg As String    Dim SentMsg As String    Dim MyLimit As Double    NotSentMsg = "No EMAIL"    SentMsg = "Send EMAIL"    'Above the MyLimit value it will run the macro    MyLimit = 0    'Set the range with the Formula that you want to check    Set FormulaRange = ThisWorkbook.Sheets("Forecast LH 100 Summary").Range("AU85")    On Error GoTo EndMacro:    For Each FormulaCell In FormulaRange.Cells        With FormulaCell            If IsNumeric(.Value) = False Then                MyMsg = "Not numeric"            Else                If .Value > MyLimit Then                    MyMsg = SentMsg                    If .Offset(0, 1).Value = NotSentMsg Then                        Call Send_Email                    End If                Else                    MyMsg = NotSentMsg                End If            End If            Application.EnableEvents = False            .Offset(0, 1).Value = MyMsg            Application.EnableEvents = True        End With    Next FormulaCellExitMacro:    Exit SubEndMacro:    Application.EnableEvents = True    MsgBox "Some Error occurred." _         & vbLf & Err.Number _         & vbLf & Err.Description                  End Sub[size=14]

    [/SIZE]

    Good Evening,


    I am hoping to find a way to write a formula that looks at a specific value in column A (in my sample "X") and then skips a row and looks at the series in Column C and returns a value of the difference of the last two entries in the series.
    Then looks for the next value of "X" in column A and repeats the calculation until there are no more values of "X" in Column A

    Re: Paste formula based on changing range


    Thank you Maqbool. It took me about an hour to back into it but it does the trick rather nicely. For some reason my wireless keyboard would not generate the Array Formula {} but once I tried the Ctrl+Shift+Enter on my laptop keyboard it worked perfectly.


    Thanks for the lesson in Arrays.:heelloo:

    [FORMULA]=(SUM(B5:G5)-(LARGE(B5:G5,1))-(SMALL(B5:G5,1)))/(COUNT(B5:G5)-2) [FORMULA]


    I am trying to run a macro on a table that will paste the above formula in the far right column.
    but when there are multiple records with the same name I would like to adjust the range in formula to include all records with that same name.


    in a unique case it will only need to apply to a single row. (e.g. [FORMULA]=(SUM(B5:G5)-(LARGE(B5:G5,1))-(SMALL(B5:G5,1)))/(COUNT(B5:G5)-2) [FORMULA])




    but if the name covers mulitpe rows I need the formula to change range to includes these rows (e.g. [FORMULA] =(SUM(B6:G9)-(LARGE(B6:G9,1))-(SMALL(B6:G9,1)))/(COUNT(B6:G9)-2) [FORMULA]

    Good Evening,


    I am trying to create set of excel file with the names from a list. I am starting with the active workbook and asking it to loop until the list is empty


    So start at box A8 and create a workbook for each entry on the list as a seperate workbook.



    I have the sameple code below


    I was hoping to adjust the below macro to run without the search function. Currently it is set to bring up a query box and ask for the search sting. I would like it to auto run and insert a page break every time it finds the word "Student" without the query box.









    thanks,


    /f