Need VBA code to Match 5 columns and mark unchanged/changed in next cell

billigee

New Member
Joined
Sep 18, 2020
Messages
24
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Dear All,

I am working with a sheet where there is a cost of products for 5 months from column A to E, I want to check if there is any change in price in any month then it should mention CHANGED in column F or UNCHANGED in column F

the criteria is TRUE figure (UNCHANGED) is

if amount in all 5 columns is same
if amount of 1 column is blank or zero and other 4 column are same
if amount of more than 1 column is blank or zero and rest of columns are same

Then the result should be UNCHANGED

otherwise it should mentioned CHANGED

to make it more clear see snapshot

1651853532321.png


Thankyou
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,317
Office Version
  1. 365
Platform
  1. Windows
Some event has to trigger that code. What action will the user take that executes the code?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
I do not understand:
1. Why would a price be Zero
2.Why would a price be blank
And this is confusing to me:
You said:
the criteria is TRUE figure (UNCHANGED) is
And why if the price is 5 for one month but zero for another month that is not a change
 

billigee

New Member
Joined
Sep 18, 2020
Messages
24
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
I do not understand:
1. Why would a price be Zero
2.Why would a price be blank
And this is confusing to me:
You said:
the criteria is TRUE figure (UNCHANGED) is
And why if the price is 5 for one month but zero for another month that is not a change
Actually it is cost of my product, if we purchase in one month and did not purchase in other month then my sheet is showing zero in that month, when I am exporting the data sometimes it shows blank cell so that's why it could be zero or blank
right now I tried with if(AND condition but my logics are so many which is not possible to write in IF THEN ELSE statement

I understand it is confusing but I have to show the only changed cost price of last 5 months,
January Is 5
feb is 0
mar is 5
apr is 5
may is0

means no change

1651859065478.png


I have posted snapshot again as there was a mistake in last row
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Actually it is cost of my product, if we purchase in one month and did not purchase in other month then my sheet is showing zero in that month, when I am exporting the data sometimes it shows blank cell so that's why it could be zero or blank
right now I tried with if(AND condition but my logics are so many which is not possible to write in IF THEN ELSE statement

I understand it is confusing but I have to show the only changed cost price of last 5 months,
January Is 5
feb is 0
mar is 5
apr is 5
may is0

means no change
So are you saying if it is zero or empty that is not a change?
 

billigee

New Member
Joined
Sep 18, 2020
Messages
24
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
So are you saying if it is zero or empty that is not a change?
no it should treat it as unchanged because we may have purchase in only one month so the price before and after remains same
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,317
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hope someone comes back. I've been trying various things for hours. Most of my time is spent researching Excel object properties and such.
Basically, I was trying to call a function and pass a range to it e.g. =PriceChange(F1:J1)
While I think I can loop over that, comparing values according to the criteria looks a bit kludgy to me.

So I opted to try assigning the range to an array since it's more efficient and I thought it would be easier to just deal with numbers. No word of a lie, I've looked over at least 20 sites and umpteen threads, all of which led to nowhere so far. A few claim that this will work:

Dim ary() As Variant
any version of the following just causes the editor to lock up (with or without using .Value on the right side of = )
ary =
ary() =

I think it is because the sheet cell is still active, as if you were editing in the formula bar. So I drag the formula down to the next row and then when
ary() = Sheets("Sheet3").Range("F1:J1") is executed (using hard coded range just to test) code execution just stops - I can interact with vb editor and sheet so nothing's locked up.

I've been at it for hours now, but happy hour has arrived and I need a break!

EDIT: forgot to mention that at one point I had something in an array (but not by using ary = "range here") and its UBound was 1. When I tried to retrieve whatever was there with ?ary(0) or ?ary(1) I got "subscript out of range" error.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
Your first image and your second image are not the same.
I can see why image 2 would work but not how image 1 would work.
Look at last row in each image
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,317
Office Version
  1. 365
Platform
  1. Windows
Your first image and your second image are not the same.
I can see why image 2 would work but not how image 1 would work.
Look at last row in each image
Isn't post 4 a correction so post 1 image should be ignored?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,381
Members
430,425
Latest member
xlsee

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
Top