VBA: notify when number switches from positive to negative and vice versa (Excel)

[zeno]

Hello,
I have a column in Excel with both positive and negative numbers. I would like to receive through VBA a notification on screen every time in the list a number switches sign from one row to the other. The notification is different with a change from positive to negative than from negative to positive. It would also have to indicate at what row that change happens.
Thank you for your help.

 

[Joe4]

What do you want to "trigger" this VBA code to run (i.e. when should it run)?

 

[zeno]

I have a base file with multiple thousand of lines on which I make calculations. In the last part of this processing, the code will go through the last column that is calculated to make this notification, every time it occurs.
At the moment I activate the code myself.

 

[Joe4]

So, it sounds like a macro that you only want to run when you call for it, and is not automatically triggered. Is that right?

 

[Joe4]

Also, what kind of notification do you want?
Do you want a separate message box for each change (if you so, you will need to close each one), or do you have some other idea?

 

[zeno]

Is it possible to have one single message box, that adds every notification it detects? In a format of for example one line per notification. That would be the best solution, rather than separate message boxes. Is such an option possible?

 

[Joe4]

Give this a try. Note you will need to change your column and starting row in the code.

Sub MySignChangeMacro()
 
    Dim myColumn As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRow As Long
    Dim myLastSign As String
    Dim myCurrentSign As String
    Dim myMsgBox As String
    
    Application.ScreenUpdating = False
    
'   Designate which column to run on
    myColumn = "B"
    
'   Designate which row to run on
    myStartRow = 2
    
'   Find last row with data in that column
    myLastRow = Cells(Rows.Count, myColumn).End(xlUp).Row
    
'   Find value of first entry
    myLastSign = CheckSign(Cells(myStartRow, myColumn).Value)
    
'   Loop through all rows
    For myRow = myStartRow + 1 To myLastRow
        myCurrentSign = CheckSign(Cells(myRow, myColumn).Value)
        If myLastSign <> myCurrentSign Then
            myMsgBox = myMsgBox & vbCrLf & "Sign change on row " & myRow
        End If
        myLastSign = myCurrentSign
    Next myRow
    
'   Return final message box
    MsgBox myMsgBox
    
    Application.ScreenUpdating = True
    
End Sub
 
Function CheckSign(myEntry As Variant) As String
'   Returns whether a value is positive or negative
    If myEntry < 0 Then
        CheckSign = "Negative"
    Else
        CheckSign = "Positive"
    End If
End Function

 

[zeno]

This code is working! Thanks so much!
I seem to have so many sign changes though, that the notification panel is too short. It is very narrow too, so that the only notification I get is "row x has a sign change".
Regarding length, is a scroll down panel possible? And for the width, is that possible too, or to make it wider, or both?

 

[Joe4]

I don't think there are format options with the Message Boxes.
Maybe it is best to write out the results to a "Log Sheet" in Excel?

 

[Joe4]

Assuming we have a sheet named "Log", that code would look like:

Sub MySignChangeMacro()
 
    Dim myColumn As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRow As Long
    Dim myLastSign As String
    Dim myCurrentSign As String
    Dim myChangeCount As Long
        
    Application.ScreenUpdating = False
    
'   Designate which column to run on
    myColumn = "B"
    
'   Designate which row to run on
    myStartRow = 2
    
'   Find last row with data in that column
    myLastRow = Cells(Rows.Count, myColumn).End(xlUp).Row
    
'   Find value of first entry
    myLastSign = CheckSign(Cells(myStartRow, myColumn).Value)
    
'   Loop through all rows
    For myRow = myStartRow + 1 To myLastRow
        myCurrentSign = CheckSign(Cells(myRow, myColumn).Value)
        If myLastSign <> myCurrentSign Then
            myChangeCount = myChangeCount + 1
            Sheets("Log").Cells(myChangeCount, "A").Formula = "Sign change on row " & myRow
        End If
        myLastSign = myCurrentSign
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub
 
Function CheckSign(myEntry As Variant) As String
'   Returns whether a value is positive or negative
    If myEntry < 0 Then
        CheckSign = "Negative"
    Else
        CheckSign = "Positive"
    End If
End Function