Change in output generated by a macro

Valentino · Apr 5, 2022

Hi everyone,

in another thread, @johnnyL has helped me tremendously by developing a code which regularly checks whether certain conditions in a spreadsheet have been fulfilled.
Every 10 minutes, the macro writes the outcome of these checks to a separate outputsheet, which alerts me to changes in conditions.
What i still would like to achieve, is to change the current notification generated by this macro.
- Current notification: "(1) Asset10" --> (indicating that Asset10 changed from "0" to "1", see blue cell in picture below)
- preferred notification: "(1) Asset10 Completed" --> (so add the value from column B in Sheet1 highlighted in yellow to the notification).

Below please find the code that i have been working with and attached a picture of Sheet1 in the workbook containing the value to be added (in yellow) .

I have been trying to adjust the code myself based on some solutions i found on this forum and elsewhere, but have struggled as after I've changed the code is bugged (my inexperience)

Would someone be able to help me to adjust the code?

Many thanks for thoughts!

Valentino

VBA Code:

Private Sub Worksheet_Calculate()
'
'   V1.2
'   1st 10 minute refresh will create the destination if it doesn't exist & will save the Formula column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current formula column to the previous formula column and display the row #s that changed to '1' or '-1'.
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
'
    Dim FormulaStartRow                 As Long, LastRowAssetColummn    As Long
    Dim DestinationSheet                As String
    Dim AssetColumn                     As String, FormulaColumn        As String
    Dim wsDestination                   As Worksheet, wsSource          As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                           ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                    ' <--- Set this to the sheetname that has the '1's & '0's
'
      FormulaColumn = "E"                                           ' <--- Set this to the formula Column letter
    FormulaStartRow = 2                                             ' <--- Set this to the start row of formulas in the FormulaColumn
       AssetColumn = "A"                                            ' <--- Set this to the Asset Column letter, this column is used to determine last row
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & Rows.Count).End(xlUp).Row ' Determine last row of data
'
    If Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "1") > 0 Or _
            Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "-1") > 0 Then  ' If the range contains any value of 1 or -1 then ...
'
        Dim DestinationSheetExists      As Boolean
        Dim FormulaColumnRow            As Long, OutputArrayRow As Long
        Dim LastDestinationColumnNumber As Long
        Dim RowOffset                   As Long
        Dim AssetColumnArray            As Variant, FormulaColumnArray          As Variant
        Dim OutputArray                 As Variant, PreviousFormulaResultArray  As Variant
'
        On Error Resume Next                                        '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)   '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                             '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True      '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(After:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Save Assets into array
        AssetColumnArray = wsSource.Range(AssetColumn & _
                FormulaStartRow & ":" & AssetColumn & _
                LastRowAssetColummn)                                '   Save the values of the Asset Column range into the 2D 1 based AssetColumnArray RC
'
' Save formulas into array
        FormulaColumnArray = wsSource.Range(FormulaColumn & _
                FormulaStartRow & ":" & FormulaColumn & _
                LastRowAssetColummn)                                '   Save the values of the formula Column range into the 2D 1 based FormulaColumnArray RC
'
        ReDim OutputArray(1 To UBound(FormulaColumnArray))          '   Establish # of rows in 1D 1 based OutputArray
'
' Create Saved formula result column on DestinationSheet
        If wsDestination.Range("A1") = vbNullString Then
            wsDestination.Range("A1") = Date                        '   Display the Date on DestinationSheet
            wsDestination.Range("A2") = Time()                      '   Display the Time on DestinationSheet
            wsDestination.Range("A3") = "------------------"        '   Display spacer line on DestinationSheet
'
            wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
                FormulaColumnArray                                  '   Display results to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit            '   Autofit all of the columns
'
            GoTo SubExit
        End If
'
' Load previous formula results into array
        PreviousFormulaResultArray = wsDestination.Range("A4:A" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)    '   Load previous formula results into PreviousFormulaResultArray
'
        OutputArrayRow = 0                                          '   Initialize OutputArrayRow to zero
        RowOffset = FormulaStartRow - LBound(FormulaColumnArray)    '   Determine Row difference between FormulaStartRow and start row of FormulaColumnArray
'
'-------------------------------------------------------------------
'
        For FormulaColumnRow = 1 To UBound(FormulaColumnArray, 1)   '   Loop through the FormulaColumnArray to check for '1's & '-1's
            If FormulaColumnArray(FormulaColumnRow, 1) = "1" Or _
                    FormulaColumnArray(FormulaColumnRow, 1) = "-1" Then '       If a '1' or '-1' is found then ...
                If PreviousFormulaResultArray(FormulaColumnRow, 1) = 0 Then '       If previous value was '0' then ...
                    OutputArrayRow = OutputArrayRow + 1                 '           Increment OutputArrayRow
'
                    OutputArray(OutputArrayRow) = "(" & _
                            FormulaColumnArray(FormulaColumnRow, 1) & _
                            ") " & AssetColumnArray(FormulaColumnRow, 1)    '           Save the changed to value & Asset into OutputArray
                End If
            End If
        Next                                                        '   Loop Back
'
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column     '   Get last Column Number used in the DestinationSheet
'
        wsDestination.Cells(1, LastDestinationColumnNumber + 1) = Date      '   Display the Date on DestinationSheet
        wsDestination.Cells(2, LastDestinationColumnNumber + 1) = Time()    '   Display the Time on DestinationSheet
        wsDestination.Cells(3, LastDestinationColumnNumber + 1) = "------------------"  '   Display spacer line on DestinationSheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                  '   Display results to DestinationSheet
'
'Save current formula results to the DestinationSheet
        wsDestination.Range("A1") = Date                            '   Display the Date on DestinationSheet
        wsDestination.Range("A2") = Time()                          '   Display the Time on DestinationSheet
        wsDestination.Range("A3") = "------------------"            '   Display spacer line on DestinationSheet
'
        wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
            FormulaColumnArray                                      '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub

johnnyL · Apr 7, 2022

Valentino said:
What i still would like to achieve, is to change the current notification generated by this macro.
- Current notification: "(1) Asset10" --> (indicating that Asset10 changed from "0" to "1", see blue cell in picture below)
- preferred notification: "(1) Asset10 Completed" --> (so add the value from column B in Sheet1 highlighted in yellow to the notification).

@Valentino The code you submitted does not produce the 'Current notification' that you mention.

So, if we rewind back to your previous request in the previous thread that I suggested you open a new thread, Excel alerts post #33.

Try the following code for that:

VBA Code:

Private Sub Worksheet_Calculate()
'
'   V2.0
'
'   1st 10 minute refresh will create the DestinationSheet if it doesn't exist & will save the conditions column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current condition columns to the previous condition columns and display the Assets that had
'       conditions changed to '1' or '-1'
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
'
    Dim ConditionsStartRow              As Long, LastRowAssetColummn    As Long
    Dim ConditionsRange                 As Range
    Dim DestinationSheet                As String
    Dim AssetColumn                     As String
    Dim FirstConditionColumn            As String, LastConditionColumn  As String
    Dim wsDestination                   As Worksheet, wsSource          As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                               ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                        ' <--- Set this to the sheetname that has the '1's & '0's
'
             AssetColumn = "A"                                          ' <--- Set this to the Asset Column letter, this column is used to determine last row
    FirstConditionColumn = "B"                                          ' <--- Set this to the column letter of the first condition
     LastConditionColumn = "D"                                          ' <--- Set this to the column letter of the last condition
      ConditionsStartRow = 2                                            ' <--- Set this to the start row of conditions
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & _
            Rows.Count).End(xlUp).Row                                   ' Determine last row of data
'
    Set ConditionsRange = wsSource.Range(FirstConditionColumn & _
            ConditionsStartRow & ":" & LastConditionColumn & _
            LastRowAssetColummn)                                        ' Establish the ConditionsRange
'
    If Application.CountIf(ConditionsRange, "1") > 0 Or _
            Application.CountIf(ConditionsRange, "-1") > 0 Then         ' If the ConditionsRange contains any value of 1 or -1 then ...
'
        Dim ArrayRowIncremented         As Boolean
        Dim DestinationSheetExists      As Boolean
        Dim ConditionsColumnColumn      As Long, ConditionsColumnRow    As Long
        Dim LastDestinationColumnNumber As Long
        Dim OutputArrayRow              As Long
        Dim AssetColumnArray            As Variant, ConditionsArray     As Variant, PreviousConditionsArray As Variant
        Dim CurrentConditionValue       As Variant
        Dim OutputArray                 As Variant
'
        On Error Resume Next                                                '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                                     '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True  '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(after:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Load current Conditions into array
        ConditionsArray = ConditionsRange                                       '   Load the values of the Condition Columns range into the 2D 1 based
'                                                                               '           ConditionsArray RC
        ReDim OutputArray(1 To UBound(ConditionsArray))                         '   Establish # of rows in 1D 1 based OutputArray
'
' Create Saved conditions result columns on DestinationSheet if they haven't been created yet
        If wsDestination.Range("A1") = vbNullString Then                        '   If previous conditions have not been saved then ...
            wsDestination.Range("A1") = Date                                    '       Display current Date on DestinationSheet
            wsDestination.Range("A2") = Time()                                  '       Display current Time on DestinationSheet
            wsDestination.Range("A3") = "------------------"                    '       Display spacer line on DestinationSheet
'
            wsDestination.Range("A4").Resize(UBound(ConditionsArray, 1), _
                    UBound(ConditionsArray, 2)) = ConditionsArray               '       Display results to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit                        '       Autofit all of the columns
'
            GoTo SubExit                                                        '       Exit this subroutine
        End If
'
' Load previous conditions results into array
        PreviousConditionsArray = wsDestination.Range("A4:C" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)            '   Load previous conditions results into PreviousConditionsArray
'
' Load Assets into array
        AssetColumnArray = wsSource.Range(AssetColumn & ConditionsStartRow & _
                ":" & AssetColumn & LastRowAssetColummn)                        '   Load the values of the Asset Column range into the 2D 1 based
'                                                                               '           AssetColumnArray RC
'-------------------------------------------------------------------
'
' Find and save changes from zero
        OutputArrayRow = 0                                                      '   Initialize OutputArrayRow to zero
'
        For ConditionsColumnRow = 1 To UBound(ConditionsArray, 1)               '   Loop through the ConditionsArray rows to check for '1's & '-1's
            For ConditionsColumnColumn = 1 To UBound(ConditionsArray, 2)        '       Loop through the ConditionsArray columns to check for '1's & '-1's
'
                CurrentConditionValue = ConditionsArray(ConditionsColumnRow, _
                        ConditionsColumnColumn)                                 '           Get the CurrentConditionValue
'
                If CurrentConditionValue = "1" Or CurrentConditionValue = "-1" Then '           If a '1' or '-1' is found then ...
'
                    If PreviousConditionsArray(ConditionsColumnRow, _
                            ConditionsColumnColumn) = 0 Then                    '               If previous value was '0' then ...
                        If ArrayRowIncremented = False Then                     '                   If we haven't already incremented OutputArrayRow then ...
                            OutputArrayRow = OutputArrayRow + 1                 '                       Increment OutputArrayRow
                            ArrayRowIncremented = True                          '                       Set ArrayRowIncremented flag = True
                        End If
'
                        If OutputArray(OutputArrayRow) = vbNullString Then              '                   If OutputArray cell is blank then ...
                            If CurrentConditionValue = "1" Then                         '                       If '1' was the change then ...
                                OutputArray(OutputArrayRow) = _
                                        AssetColumnArray(ConditionsColumnRow, 1) & _
                                        ": Plus-Condition" & ConditionsColumnColumn     '                           Save asset name & condition# that changed
                            ElseIf CurrentConditionValue = "-1" Then                    '                       Else if '-1' was the change then ...
                                OutputArray(OutputArrayRow) = _
                                        AssetColumnArray(ConditionsColumnRow, 1) & _
                                        ": Minus-Condition" & ConditionsColumnColumn    '                           Save asset name & condition# that changed
                            End If
                        Else                                                            '                   Else
                            If CurrentConditionValue = "1" Then                         '                       If '1' was the change then ...
                                OutputArray(OutputArrayRow) = OutputArray(OutputArrayRow) & _
                                        ", Plus-Condition" & ConditionsColumnColumn     '                           Append the condition# that changed
                            ElseIf CurrentConditionValue = "-1" Then                    '                       Else if '-1' was the change then ...
                                OutputArray(OutputArrayRow) = OutputArray(OutputArrayRow) & _
                                        ", Minus-Condition" & ConditionsColumnColumn    '                           Append the condition# that changed
                            End If
                        End If
                    End If
                End If
            Next                                                                        '       Loop Back
'
            ArrayRowIncremented = False                                         '       Reset the ArrayRowIncremented to False
        Next                                                                    '   Loop Back
'
'-------------------------------------------------------------------
'
' Save found changes to the DestinationSheet
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column                 '   Get last Column Number used in the DestinationSheet
'
        wsDestination.Cells(1, LastDestinationColumnNumber + 1) = Date          '   Display the Date on DestinationSheet
        wsDestination.Cells(2, LastDestinationColumnNumber + 1) = Time()        '   Display the Time on DestinationSheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                              '   Display results to DestinationSheet
'
'-------------------------------------------------------------------
'
'Save ConditionsArray to the DestinationSheet
        wsDestination.Range("A1") = Date                                        '   Display the Date on DestinationSheet
        wsDestination.Range("A2") = Time()                                      '   Display the Time on DestinationSheet
'
        wsDestination.Range("A4").Resize(UBound(ConditionsArray, 1), _
                UBound(ConditionsArray, 2)) = ConditionsArray                   '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                            '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub

As I mentioned in the other thread, it requires different code to accomplish what you asked for there.

Valentino · Apr 8, 2022

johnnyL said:

@Valentino The code you submitted does not produce the 'Current notification' that you mention.

So, if we rewind back to your previous request in the previous thread that I suggested you open a new thread, Excel alerts post #33.

Try the following code for that:

VBA Code:

Private Sub Worksheet_Calculate()
'
'   V2.0
'
'   1st 10 minute refresh will create the DestinationSheet if it doesn't exist & will save the conditions column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current condition columns to the previous condition columns and display the Assets that had
'       conditions changed to '1' or '-1'
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
'
    Dim ConditionsStartRow              As Long, LastRowAssetColummn    As Long
    Dim ConditionsRange                 As Range
    Dim DestinationSheet                As String
    Dim AssetColumn                     As String
    Dim FirstConditionColumn            As String, LastConditionColumn  As String
    Dim wsDestination                   As Worksheet, wsSource          As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                               ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                        ' <--- Set this to the sheetname that has the '1's & '0's
'
             AssetColumn = "A"                                          ' <--- Set this to the Asset Column letter, this column is used to determine last row
    FirstConditionColumn = "B"                                          ' <--- Set this to the column letter of the first condition
     LastConditionColumn = "D"                                          ' <--- Set this to the column letter of the last condition
      ConditionsStartRow = 2                                            ' <--- Set this to the start row of conditions
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & _
            Rows.Count).End(xlUp).Row                                   ' Determine last row of data
'
    Set ConditionsRange = wsSource.Range(FirstConditionColumn & _
            ConditionsStartRow & ":" & LastConditionColumn & _
            LastRowAssetColummn)                                        ' Establish the ConditionsRange
'
    If Application.CountIf(ConditionsRange, "1") > 0 Or _
            Application.CountIf(ConditionsRange, "-1") > 0 Then         ' If the ConditionsRange contains any value of 1 or -1 then ...
'
        Dim ArrayRowIncremented         As Boolean
        Dim DestinationSheetExists      As Boolean
        Dim ConditionsColumnColumn      As Long, ConditionsColumnRow    As Long
        Dim LastDestinationColumnNumber As Long
        Dim OutputArrayRow              As Long
        Dim AssetColumnArray            As Variant, ConditionsArray     As Variant, PreviousConditionsArray As Variant
        Dim CurrentConditionValue       As Variant
        Dim OutputArray                 As Variant
'
        On Error Resume Next                                                '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                                     '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True  '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(after:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Load current Conditions into array
        ConditionsArray = ConditionsRange                                       '   Load the values of the Condition Columns range into the 2D 1 based
'                                                                               '           ConditionsArray RC
        ReDim OutputArray(1 To UBound(ConditionsArray))                         '   Establish # of rows in 1D 1 based OutputArray
'
' Create Saved conditions result columns on DestinationSheet if they haven't been created yet
        If wsDestination.Range("A1") = vbNullString Then                        '   If previous conditions have not been saved then ...
            wsDestination.Range("A1") = Date                                    '       Display current Date on DestinationSheet
            wsDestination.Range("A2") = Time()                                  '       Display current Time on DestinationSheet
            wsDestination.Range("A3") = "------------------"                    '       Display spacer line on DestinationSheet
'
            wsDestination.Range("A4").Resize(UBound(ConditionsArray, 1), _
                    UBound(ConditionsArray, 2)) = ConditionsArray               '       Display results to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit                        '       Autofit all of the columns
'
            GoTo SubExit                                                        '       Exit this subroutine
        End If
'
' Load previous conditions results into array
        PreviousConditionsArray = wsDestination.Range("A4:C" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)            '   Load previous conditions results into PreviousConditionsArray
'
' Load Assets into array
        AssetColumnArray = wsSource.Range(AssetColumn & ConditionsStartRow & _
                ":" & AssetColumn & LastRowAssetColummn)                        '   Load the values of the Asset Column range into the 2D 1 based
'                                                                               '           AssetColumnArray RC
'-------------------------------------------------------------------
'
' Find and save changes from zero
        OutputArrayRow = 0                                                      '   Initialize OutputArrayRow to zero
'
        For ConditionsColumnRow = 1 To UBound(ConditionsArray, 1)               '   Loop through the ConditionsArray rows to check for '1's & '-1's
            For ConditionsColumnColumn = 1 To UBound(ConditionsArray, 2)        '       Loop through the ConditionsArray columns to check for '1's & '-1's
'
                CurrentConditionValue = ConditionsArray(ConditionsColumnRow, _
                        ConditionsColumnColumn)                                 '           Get the CurrentConditionValue
'
                If CurrentConditionValue = "1" Or CurrentConditionValue = "-1" Then '           If a '1' or '-1' is found then ...
'
                    If PreviousConditionsArray(ConditionsColumnRow, _
                            ConditionsColumnColumn) = 0 Then                    '               If previous value was '0' then ...
                        If ArrayRowIncremented = False Then                     '                   If we haven't already incremented OutputArrayRow then ...
                            OutputArrayRow = OutputArrayRow + 1                 '                       Increment OutputArrayRow
                            ArrayRowIncremented = True                          '                       Set ArrayRowIncremented flag = True
                        End If
'
                        If OutputArray(OutputArrayRow) = vbNullString Then              '                   If OutputArray cell is blank then ...
                            If CurrentConditionValue = "1" Then                         '                       If '1' was the change then ...
                                OutputArray(OutputArrayRow) = _
                                        AssetColumnArray(ConditionsColumnRow, 1) & _
                                        ": Plus-Condition" & ConditionsColumnColumn     '                           Save asset name & condition# that changed
                            ElseIf CurrentConditionValue = "-1" Then                    '                       Else if '-1' was the change then ...
                                OutputArray(OutputArrayRow) = _
                                        AssetColumnArray(ConditionsColumnRow, 1) & _
                                        ": Minus-Condition" & ConditionsColumnColumn    '                           Save asset name & condition# that changed
                            End If
                        Else                                                            '                   Else
                            If CurrentConditionValue = "1" Then                         '                       If '1' was the change then ...
                                OutputArray(OutputArrayRow) = OutputArray(OutputArrayRow) & _
                                        ", Plus-Condition" & ConditionsColumnColumn     '                           Append the condition# that changed
                            ElseIf CurrentConditionValue = "-1" Then                    '                       Else if '-1' was the change then ...
                                OutputArray(OutputArrayRow) = OutputArray(OutputArrayRow) & _
                                        ", Minus-Condition" & ConditionsColumnColumn    '                           Append the condition# that changed
                            End If
                        End If
                    End If
                End If
            Next                                                                        '       Loop Back
'
            ArrayRowIncremented = False                                         '       Reset the ArrayRowIncremented to False
        Next                                                                    '   Loop Back
'
'-------------------------------------------------------------------
'
' Save found changes to the DestinationSheet
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column                 '   Get last Column Number used in the DestinationSheet
'
        wsDestination.Cells(1, LastDestinationColumnNumber + 1) = Date          '   Display the Date on DestinationSheet
        wsDestination.Cells(2, LastDestinationColumnNumber + 1) = Time()        '   Display the Time on DestinationSheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                              '   Display results to DestinationSheet
'
'-------------------------------------------------------------------
'
'Save ConditionsArray to the DestinationSheet
        wsDestination.Range("A1") = Date                                        '   Display the Date on DestinationSheet
        wsDestination.Range("A2") = Time()                                      '   Display the Time on DestinationSheet
'
        wsDestination.Range("A4").Resize(UBound(ConditionsArray, 1), _
                UBound(ConditionsArray, 2)) = ConditionsArray                   '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                            '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub

As I mentioned in the other thread, it requires different code to accomplish what you asked for there.

Hi Johnny,

thanks for the answer! I thought in the opening post i copied the code as i'm using it currently, but if not sorry for that

I tried the code immediately, and i can see that the notification indeed has changed: now it gives back eg "Asset 3: Plus-Condition2".
Are you saying that based on this code, it is not possible to give back eg ""Asset 3: Plus-Completed"? so "Completed" instead of "Condition2" where "Completed" is the value in column B of the row where the change has occurred?

Thanks again,
Valentino

johnnyL · Apr 8, 2022

That is certainly doable. My question to you would be why do you want it to show 'completed'? Wouldn't that be assumed if it is triggering a change?

You are basically creating a list of assets that show as completed in the column B and then you want to show 'Completed' in every asset in the completed list?

Valentino · Apr 8, 2022

johnnyL said:
That is certainly doable. My question to you would be why do you want it to show 'completed'? Wouldn't that be assumed if it is triggering a change?

You are basically creating a list of assets that show as completed in the column B and then you want to show 'Completed' in every asset in the completed list?

Good question, which indeed needs explanation. The value in column B is not always equal to "Completed", its not a constant but dynamic/ a formula (other statusses are "semi-completed", "started" and "Prospect").

johnnyL · Apr 8, 2022

So ??? You would want any value other than "0" in column B to be counted as a change if it was previously "0"?

Valentino · Apr 8, 2022

johnnyL said:
So ??? You would want any value other than "0" in column B to be counted as a change if it was previously "0"?

I realise now i made it not clear again, apologies... see attachment to the opening post - columns c and d contain conditions, whenever these change from "0" into "-1" or "1" that constitutes the trigger --> a notification is put on the TenMinutes sheet. Column b is not a trigger but only a specification of why a condition is triggered in column c or d, and i would like that to be included in the notification.
Hope is more clear now, if not let me know, thanks...

johnnyL · Apr 8, 2022

Ok so you mentioned in the attachment you would like to see '(1) Asset 10 Completed' as an example. What does the '(1)' represent? The value from column E?

Valentino · Apr 9, 2022

johnnyL said:
Ok so you mentioned in the attachment you would like to see '(1) Asset 10 Completed' as an example. What does the '(1)' represent? The value from column E?

Correct, columns c and d are 2 conditions that are combined (by means of a formula) in column E - once one of the conditions change from "0" to "1" or "-1", this changed value is reflected in column E and triggers a notification. Indeed then the (1) or (-1) in the notification represents the changed value in column e.
Column b does not have any dependents btw.

johnnyL · Apr 11, 2022

Apologies for forgetting about this thread.

It is way past my bedtime, but I put the following together that I think does what you want, at least it does at this late hour of my thinking:

VBA Code:

Private Sub Worksheet_Calculate()
'
'   V2.1
'
'   1st 10 minute refresh will create the DestinationSheet if it doesn't exist & will save the conditions column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current condition columns to the previous condition columns and display the Assets that had
'       conditions changed to '1' or '-1'
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
    Dim CurrentConditionsStartRow               As Long, LastRowAssetColummn                As Long
    Dim CurrentConditionsRange                  As Range
    Dim DestinationSheet                        As String
    Dim AssetColumn                             As String, StatusColumn                     As String
    Dim FirstConditionColumn                    As String, SecondConditionColumn            As String
    Dim ConditionsCombinedColumn                As String
    Dim wsDestination                           As Worksheet, wsSource                      As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                               ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                        ' <--- Set this to the sheetname that has the '1's & '0's
'
                  AssetColumn = "A"                                     ' <--- Set this to the Asset Column letter, this column is used to determine last row
                 StatusColumn = "B"                                     ' <--- Set this to the column letter of the StatusColumn
         FirstConditionColumn = "C"                                     ' <--- Set this to the column letter of the first condition
        SecondConditionColumn = "D"                                     ' <--- Set this to the column letter of the second condition
     ConditionsCombinedColumn = "E"                                     ' <--- Set this to the column letter of the ConditionsCombined Column
    CurrentConditionsStartRow = 2                                       ' <--- Set this to the start row of CurrentConditions
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & _
            Rows.Count).End(xlUp).Row                                   ' Determine last row of data
'
    Set CurrentConditionsRange = wsSource.Range(FirstConditionColumn & _
            CurrentConditionsStartRow & ":" & SecondConditionColumn & _
            LastRowAssetColummn)                                        ' Establish the ConditionsRange
'
    If Application.CountIf(CurrentConditionsRange, "1") > 0 Or _
            Application.CountIf(CurrentConditionsRange, "-1") > 0 Then         ' If the ConditionsRange contains any value of 1 or -1 then ...
'
        Dim ArrayRowIncremented                 As Boolean, DestinationSheetExists          As Boolean
        Dim ConditionsColumnColumn              As Long, ConditionsColumnRow                As Long
        Dim CurrentConditionValue               As Long
        Dim LastDestinationColumnNumber         As Long
        Dim OutputArrayRow                      As Long
'
        Dim AssetColumnArray                    As Variant, CurrentConditionsArray          As Variant
        Dim DateTimeArray(1 To 2)               As Variant
        Dim PreviousConditionsArray             As Variant, PreviousHeadingsArray(1 To 3)   As Variant
        Dim OutputArray                         As Variant, SourceArray                     As Variant
'
        On Error Resume Next                                                '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                                     '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True  '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(after:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Load current Conditions into array
        CurrentConditionsArray = CurrentConditionsRange                         '   Load the values of the Condition Columns range into the 2D 1 based
'                                                                               '           ConditionsArray RC
        ReDim OutputArray(1 To UBound(CurrentConditionsArray))                  '   Establish # of rows in 1D 1 based OutputArray
'
        SourceArray = wsSource.Range(AssetColumn & CurrentConditionsStartRow & _
                ":" & ConditionsCombinedColumn & LastRowAssetColummn)           '   Load all source values into SourceArray
'
' Create Saved conditions result columns on DestinationSheet if they haven't been created yet
        If wsDestination.Range("A1") = vbNullString Then                        '   If previous conditions have not been saved then ...
            PreviousHeadingsArray(1) = Date                                     '       Save Date into PreviousHeadingsArray
            PreviousHeadingsArray(2) = Time()                                   '       Save Time into PreviousHeadingsArray
            PreviousHeadingsArray(3) = "------------------"                     '       Save space line into PreviousHeadingsArray
            wsDestination.Range("A1").Resize(UBound(PreviousHeadingsArray, 1)) _
                    = Application.Transpose(PreviousHeadingsArray)              '       Save PreviousHeadingsArray to destination sheet
'
            wsDestination.Range("A4").Resize(UBound(CurrentConditionsArray, 1), _
                    UBound(CurrentConditionsArray, 2)) = CurrentConditionsArray '       Display CurrentConditionsArray to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit                        '       Autofit all of the columns
'
            GoTo SubExit                                                        '       Exit this subroutine
        End If
'
' Load previous conditions results into array
        PreviousConditionsArray = wsDestination.Range("A4:B" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)            '   Load previous conditions results into PreviousConditionsArray
'                                                                               '           AssetColumnArray RC
'-------------------------------------------------------------------
'
' Find and save changes from zero
        OutputArrayRow = 0                                                      '   Initialize OutputArrayRow to zero
'
        For ConditionsColumnRow = 1 To UBound(CurrentConditionsArray, 1)        '   Loop through the CurrentConditionsArray rows to check for '1's & '-1's
            For ConditionsColumnColumn = 1 To UBound(CurrentConditionsArray, 2) '       Loop through the CurrentConditionsArray columns to check for '1's & '-1's
'
                CurrentConditionValue = CurrentConditionsArray(ConditionsColumnRow, _
                        ConditionsColumnColumn)                                 '           Get the CurrentConditionValue
'
                If CurrentConditionValue = "1" Or CurrentConditionValue = "-1" Then '           If a '1' or '-1' is found then ...
'
                    If PreviousConditionsArray(ConditionsColumnRow, _
                            ConditionsColumnColumn) = 0 Then                    '               If previous value was '0' then ...
                        If ArrayRowIncremented = False Then                     '                   If we haven't already incremented OutputArrayRow then ...
                            OutputArrayRow = OutputArrayRow + 1                 '                       Increment OutputArrayRow
                            ArrayRowIncremented = True                          '                       Set ArrayRowIncremented flag = True
                        End If
'
                        If OutputArray(OutputArrayRow) = vbNullString Then      '                   If OutputArray cell is blank then ...
                            OutputArray(OutputArrayRow) = "(" & _
                            SourceArray(ConditionsColumnRow, 5) & ") " & _
                            SourceArray(ConditionsColumnRow, 1) & " " & _
                            SourceArray(ConditionsColumnRow, 2)                 '                       Save desired result to OutputArray
                        End If
                    End If
                End If
            Next                                                                '       Loop Back
'
            ArrayRowIncremented = False                                         '       Reset the ArrayRowIncremented to False
        Next                                                                    '   Loop Back
'
'-------------------------------------------------------------------
'
' Save found changes to the DestinationSheet
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column                 '   Get last Column Number used in the DestinationSheet
'
        DateTimeArray(1) = Date
        DateTimeArray(2) = Time()
        wsDestination.Cells(1, LastDestinationColumnNumber + _
                1).Resize(UBound(DateTimeArray, 1)) = _
                Application.Transpose(DateTimeArray)                            '   Display Date & Time to Destination sheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                              '   Display results to DestinationSheet
'
'-------------------------------------------------------------------
'
'Save ConditionsArray to the DestinationSheet
        wsDestination.Range("A1").Resize(UBound(DateTimeArray, 1)) _
                    = Application.Transpose(DateTimeArray)                      '   Display Date & Time to destination sheet
'
        wsDestination.Range("A4").Resize(UBound(CurrentConditionsArray, 1), _
                UBound(CurrentConditionsArray, 2)) = CurrentConditionsArray     '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                            '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub

I redid some of the code, so hopefully it didn't go sideways in my late hour thinking.

Let us know how it goes.

Change in output generated by a macro

Valentino

Board Regular

Attachments

Excel Facts

johnnyL

Well-known Member

Valentino

Board Regular

johnnyL

Well-known Member

Valentino

Board Regular

johnnyL

Well-known Member

Valentino

Board Regular

johnnyL

Well-known Member

Valentino

Board Regular

johnnyL

Well-known Member

Similar threads

Forum statistics

Share this page

Change in output generated by a macro

Board Regular

Attachments

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock