On 1/4/2018 at 11:11:55 PM GS wrote:

On 1/4/2018 at 7:00:30 PM GS wrote:

I am using Microsoft Excel 2010.

Several years ago, Claus Busch wrote the following macro for me:

Sub roundSelection()
Dim pctChange As Double
Dim Cell As Range
pctChange = Range("pctChange")
For Each Cell In Selection
Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)
Next Cell
End Sub

When I try to run it now, I get "Run-time error '13': Type
mismatch". The problem seems to be this line of code:

Cell = WorksheetFunction.Round(Cell * (1 + pctChange), 2)

...but I don't know how to fix it as I am no expert with macros!

The macro should work like this: If one of the worksheet tabs
has a box named "pctChange" and a % value underneath that box,
it should apply the % value to all the worksheet boxes in that
tab that are formatted as currency (i.e. $ - U.S. Dollars in my
case).

I should be able to specify a different "pctChange" value for
each worksheet tab and then run the macro.

Can anybody please help? I'm willing to post the spreadsheet
somewhere, if that helps.

Thanks.

Are any of the selected cells non-numeric?

The way I remember how to run the macro (it has been many years!!),
I did not have to select any cells. The macro would look for cells
that are formatted as currency (like U.S. Dollar) and apply the
value specified by the "pctChange" variable.

It would be impractical for me to select all cells in a tab that are
formatted as currency as I have such values spread out everywhere.

Perhaps the macro, as shown above, doesn't do what I am saying it
should do. Several years have passed since the last time I ran
it... If so, would it be possible for somebody to write a macro
the way I want it now?

Since worksheets don't have any boxes (only cells), you'd be better
served to post a download link to your file...

Yes, I'll be glad to.
I posted file "test.xlsm" to https://ufile.io/n9ibi

In each worksheet of the Excel workbook you will see two cells colored
in red. One is called "pctChange" and the other one has a % value.

For instance, in worksheet "Sorted", the two boxes are located in cells
M1 and M2. In worksheet "Cyl (BSP)" they are located in cells O1 and
O2, and so on and so on.

I have inserted some test values in each "pctChange" cells. Some are
negative, some positive, some are 0%. That should allow for testing
all the possibilities.

What I am looking for is a macro that parses each worksheet
_separately_ and for each cell that is formatted as currency ($ - U.S.
Dollars) applies the "pctChange" value specified in that worksheet.

I hope that it is possible to create a macro like that...

Thanks.
--
tb