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

Status
Not open for further replies.

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What do you want to "trigger" this VBA code to run (i.e. when should it run)?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Give this a try. Note you will need to change your column and starting row in the code.
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Assuming we have a sheet named "Log", that code would look like:
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 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
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top