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]

Just replace this part:

'   Designate which row to run on
    myStartRow = 2

with this:

'   Find starting row of data
    For myStartRow = 2 To Rows.Count
        If Len(Cells(myStartRow, myColumn)) > 0 Then Exit For
    Next myStartRow

This will find the first non-blank row (starting in row 5), and start the process from that point.

 

[zeno]

Thank you Joe4!
I have a log with the correct starting point.
Very much appreciated! I've learned a lot from you.
This is not the final destination for me. I have to include more changes, but you helped me over a serious roadblock!

 

[zeno]

I'm looking at how to insert a title in the Log sheet. Since vba makes a loop through all the rows, I would always insert a title if I put the title in the loop. But since it's making calculations on the other sheet, it seems that I have to specify every action with the sheet name, so that the vba is clear on what to do where.
So how can I insert the title in sheet 'Log' and then return to the initial sheet for further analysis of sign changes?

 

[zeno]

I'm looking to transfer data from the source sheet to the destination sheet, corresponding to every row that is selected with the sign change algorithm? I would need the data from columns A & B?

 

[Joe4]

So how can I insert the title in sheet 'Log' and then return to the initial sheet for further analysis of sign changes?

You can just specify the title before the loop like this:

Sheets("Log").Range("A1")="My Column A Title"
Sheets("Log").Range("B1")="My Column B Title"

Just make sure if you are using a title in row one, that you increase the place where you are placing the data by one row, i.e.

Sheets("Log").Cells(myChangeCount+1, "A").Formula = "Sign change on row " & myRow

I'm looking to transfer data from the source sheet to the destination sheet, corresponding to every row that is selected with the sign change algorithm? I would need the data from columns A & B?

If I understand what you are asking, I bet you can figure this one out. We are already kind of doing that by writing out the log sheet, so it shouldn't be too hard to modify it to give you whatever else you need.

Give it a shot! That's the best way to learn. If you run into issues, post what you have tried.

 

[zeno]

In trying to copy to corresponding value of selected rows of previous sheet to the 'Log' sheet, I added the following line:

Sheets("Log").Cells(myChangeCount + 1, "A").Formula = Cells(myChangeCount + 1, "A").Value

However, when I ran the code, it would add values for that column consecutively, always increasing by 1, rather than only taking the selections from the lines in the log.
Should I add more code and variables? Or is the formula enough but does it just need an additional identifier?
Also, I have put the selected lines in column "D" rather than column "A".

 

[Joe4]

However, when I ran the code, it would add values for that column consecutively, always increasing by 1, rather than only taking the selections from the lines in the log.
Should I add more code and variables? Or is the formula enough but does it just need an additional identifier?

Sorry, I haven't been around much the past few days.

I am not quite sure I understand what you are saying. Can you describe an example of what is happening?

 

[Rickz]

Hello @Joe4 ,

Thank you very much for the above code, but I have a further doubt in related to the code.

In the above VBA code you have provided the code in which we were able to see the different rows at which the sign change occurs at the end. But could you please show how is it possible to get the corresponding values from the cells (of a column K) corresponding to that rows (in which the sign change occurs).

Thanks in advance

 

[Joe4]

Duplicate to: Obtaining the cell value when the numbers in a particular column switches from positive to negative and vice versa (Excel)

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.