VBA function doesn't work properly

I can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result. Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.

Is there any explanation?

Only one cell is ever "Active" so your UDF in it's present form can only ever return the string for the active cell, you need therefore to pass a range reference to the UDF so it knows to which data it should be referring when generating the string, eg:

Please Login or Register  to view this content.

Called from your cell as:

Please Login or Register  to view this content.

You could do the same thing with native functions if preferred...

Please Login or Register  to view this content.

You could certainly write code to do it but IMO the PT is an effective approach given that in essence the code "could" be relatively slow given the need to either a) iterate records or b) apply filters etc...
all in all a subroutine seems a little unnecessary given the PT gives you the requisite info to populate your final output easily enough.