Change cell.value quicker

I need to change the cell values in a column if they are more than 7 or less than -7. I've got three lots of code which work, but are very slow. Two take more than 5 minutes to run and the following takes 1.5 minutes. There is 1300 rows in my test worksheet, but in practice there will be 4-5 times that.

Is there a quicker way please?

Please Login or Register  to view this content.

Hi swallis,

The median function in excel is what you seek. No VBA needed.

=MEDIAN(-7,A2,7)

See the attached

Median betwean 7s.xlsx

Looping takes a lot of time. Copying takes a lot less time. This code assumes that there is data in columns A-C and that there is a filter on the range.

Please Login or Register  to view this content.

Hi Steve,

The Median() function returns the Value of the Number in the middle. So the formula should be =Median(-7,D2,7). I needed to think hard about this a few years ago. If you are given Median(-7,20,7) then 20>7 so 7 is in the middle and it returns 7. If you are given Median(-7,5,7) then the 5 is the middle value and is returned. If you are given Median(-7,-12,7) then the -7 is in the middle and returned.

Also the formula could be written as Median(D2,7,-7) or Median(7,-7,D2) as it uses the values of the arguments and not their position in the function.

I hope that explains Median a bit better.

If you don't use a helper column you will need to do the problem using VBA and overwrite each value. That would take longer than a helper, using the formula and then copy and paste the answer using Values Only.

Please Login or Register  to view this content.

Originally Posted by MarvinP

If you don't use a helper column you will need to do the problem using VBA and overwrite each value.

Thanks Marvin. That's what I want to do. Protonleah's solution does it nicely and still only 2 seconds.

Thanks a lot once again for you help. I'll mark it solved.

Regards,

Steve

I like the MEDIAN approach. It's a completely different box than I was thinking in.