replace cell with above value if not empty

hello friends ,
i have series of numbers in column a
i would like to replace "#value" with above value if not blank cell , with change in value of -4
if above cell is empty then it takes below cell values and minus -4

Try this:

Please Login or Register  to view this content.

What is the error message? Does your data look exactly as you have shown in your example? The code worked for me based upon the example presented. If your data sheet is different then I suggest you post a copy of your data sheet (upload) so that the code can be tweaked. It is difficult for a doctor to diagnose a patients issues if he cannot examine the patient. Same theory applies here.

i just took different values,

#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
-18

In your current example, you start with #value. Since there is no value directly above or beneath it, what should the new value be? This example does not fall into the criteria you asked for in your first post.

Attachment 364028


sorry alansidman , for inconvience ,

i have attach sample file ...

also ,

if order is in

-18
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

its works perfectly

but if order is in
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
-18

it doesnt replace with lower cell values ,
can we do something about this too ?

In your original post you indicated that if after a break, then the macro should look to the next cell below and subtract 4. Your current scenario does not allow this as it is also and error record. What should your criteria be if this is the case? Obviously, you cannot subtract 4 from a #value. Please elaborate on your criteria.

Maybe ...

Please Login or Register  to view this content.

See if this works for you:

Please Login or Register  to view this content.

thanks alansidman, its working fine , but only one small fix needed ,

I'm out of ideas on how to make this work. Will seek additional help from others in the forum.

ok sir , thanks for your efforts.

but please go through one last time to file attach ,

so you will idea of what i am trying to do ,

its my mistake , i dint posted question properly for answer i expected.

Attachment 364175

Hi,

Try this code for your sample file at post #14 :

Please Login or Register  to view this content.

Regards