Counting Cell value change in a column using macros

[anshul1516]

I am working on making a counter for a column.
The values in that column are either 0(off) or 1(on).

I want to know the count of how many times the switch goees from off to on(0 to1).

I have tried countf, sumproduct but none have worked out.

I then found a manual solution & used =IF(Cell2=0,IF(Cell1=1,1,0),0) in a New column next to it.

Then did a sum of the new column.

Can someone help work out a macro on this?

8EDIT* I also tried recording a macro but it wasn't working properly snce I could not get the same last row used in different files.

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[anshul1516]

Files have been attached. Thank you for your help.

[:) Sixthsense :)]

Using a single cell formula without any helper column and all

Please Login or Register  to view this content.

[anshul1516]

I have a set of similar files, so is it possible to create a macro which will do the same calculation?
Because each file has different last rows.

I really appreciate the help.

[:) Sixthsense :)]

What is your exact excel version? Is that values are always stay in Column-A & Column-B? Can we determine the last row based on the Column-B Data?

[anshul1516]

In the exact data,
the Column which I need the macro for is column AO always.
The last row used is of the last row of AO only.
The Excel I use is Office 2010.

[:) Sixthsense :)]

Thanks for the confirmation at the same time please update your profile.

You can refer the suggested formula to whole columns too...

Please confirm the next column address whether it is AP?

[anshul1516]

Yes,the next column AP should be inserted & contain the count.

Thanks!
I will update my profile as well.

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[anshul1516]

Thank you so much for your help. It works perfectly.

I modified it a bit by removing the First IF condition.

Thanks again!

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.