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

[billigee]

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

Thankyou

 

[Micron]

Some event has to trigger that code. What action will the user take that executes the code?

 

[My Aswer Is This]

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]

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

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

 

[My Aswer Is This]

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]

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]

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]

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]

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?

 

[My Aswer Is This]

Isn't post 4 a correction so post 1 image should be ignored?

I did not notice that comment. Thanks for pointing that out