Macro or function that changes value of selected range or ranges of cells

I don't know if anyone has done this before. I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.

I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.

Has anyone ever done something similar? Is it hard to program?

Your thoughts appreciated.
Y.

This is essentially to mimic the existing Paste Special: Operation method, yes ?

Does something like the below do what you want ?

Please Login or Register  to view this content.

The awkward bit really is how you would determine whether you were altering by Unit or % without using an additional dialog... ie some percentages could exceed 1/-1 and similar some Unit increases could be between -1/1 so based on a numerical adjustment alone one can't determine the nature of the adjustment.

Please post a sample file illustrating the code failure in action.... you would get an error if no numbers were found in selected range but that's not the error in this case.

DO, methinks if rngArea contains multi-cell areas, the IIF statement will fail.

Not sure I follow shg... can you elaborate ?
EDIT: do you mean merged cells ? If so yes I agree... merged cells will cause debug.

I did notice that if the selection were a single cell the code seemingly applies itself to use the Used Range which is, to me, weird... could you please explain to me why that is ?

Type mismatch error:

Please Login or Register  to view this content.

doh, yes, it should be cell orientated as opposed to area.... though I suspect you could extend Area approach via Evaluate ... do you have an elegant approach in mind shg

thanks

do you have an elegant approach in mind shg

Nope. Richard Schollar is very good with the use of Evaluate in lieu of loops.

For the benefit of others ...

Bill Jelen has just a published a book which illustrates the concept (with additional pointers from ExcelForum's very own: lecxe), ie using Evaluate as an alternative to a Loop.

ie to add 1 to a range of cells without looping...

Please Login or Register  to view this content.

obviously things are a little more complex in the real case though I suspect that the volume of cells to be iterated (w/SpecialCells restriction) means a Loop on an individual cell by cell basis won't be too painful...however...:

Please Login or Register  to view this content.

Very nice.

I wonder if perhaps the below will work for you ?
(additional code in red)

Please Login or Register  to view this content.