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.
--
tb

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?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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?
--
tb

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...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

For starters, the named range "pctChange" is global (workbook scope) meaning
its definition always refers to 'Cyl(BSP)'!$O$2. This name will need to be
redifined to sheet level scope for each sheet it is used on for code to work.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

After you have defined "pctChange" with local scope to each sheet the name is
used on, follow these steps to complete your project.

Step1
Make this change in your Tools==Options dialog:

Place a check in "Require Variable Declaration"


Step2
Replace everything in your code module with...

Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim wks As Worksheet, rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In wks.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then _
crng.Value = WorksheetFunction.Round(crng * (1 + rng), 2)
Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops.., didn't fully cleanup after testing! The working code follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2)
Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi,

Am Fri, 5 Jan 2018 14:13:57 +0000 (UTC) schrieb tb:

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.

try:

Sub Test()
Dim wsh As Worksheet
Dim rngc As Range, c As Range
Dim factor As Double
Dim strFormat As String

strFormat = """$""#,##0.00_);(""$""#,##0.00)"

For Each wsh In Worksheets
With wsh
Set c = .UsedRange.Find("pctChange", LookIn:=xlValues)
If Not c Is Nothing Then factor = 1 + c.Offset(1, 0)
If c Is Nothing Or factor = 1 Then GoTo Skip
For Each rngc In .UsedRange
If rngc.NumberFormat = strFormat And IsNumeric(rngc) Then
rngc = Round(rngc * factor, 2)
End If
Next
End With
Skip:
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Thanks I will try the code out. I'm having problems understanding how
to declare "pctChange" with local scope. I've never created a macro
myself and cannot find an example of such declaration with Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...

--
tb

On 1/6/2018 at 12:25:03 PM Claus Busch wrote:

Hi,

Am Fri, 5 Jan 2018 14:13:57 +0000 (UTC) schrieb tb:

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.

try:

Sub Test()
Dim wsh As Worksheet
Dim rngc As Range, c As Range
Dim factor As Double
Dim strFormat As String

strFormat = """$""#,##0.00_);(""$""#,##0.00)"

For Each wsh In Worksheets
With wsh
Set c = .UsedRange.Find("pctChange", LookIn:=xlValues)
If Not c Is Nothing Then factor = 1 + c.Offset(1, 0)
If c Is Nothing Or factor = 1 Then GoTo Skip
For Each rngc In .UsedRange
If rngc.NumberFormat = strFormat And IsNumeric(rngc) Then
rngc = Round(rngc * factor, 2)
End If
Next
End With
Skip:
Next
End Sub


Regards
Claus B.

Thanks. I will test your code and see what happens.

--
tb

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Thanks I will try the code out. I'm having problems understanding how
to declare "pctChange" with local scope. I've never created a macro
myself and cannot find an example of such declaration with Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...

On the *Formulas* tab, use *Name Manager* and set the scope to the %cell on the
sheet that's active. Activate the next sheet and select its %cell and repeat.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 1/8/2018 at 2:10:26 PM GS wrote:

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Thanks I will try the code out. I'm having problems understanding
how to declare "pctChange" with local scope. I've never created a
macro myself and cannot find an example of such declaration with
Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...

On the Formulas tab, use *Name Manager* and set the scope to the
%cell on the sheet that's active. Activate the next sheet and select
its %cell and repeat.

Garry:

Your macro seems to work fine. The only problem that I've found is
that it puts a "$0.00" in every blank cell that is formatted as
currency. If a cell is blank (and is formatted as currency), I'd like
it to stay so.

Below is the complete macro as you created it for me. Would you mind
seeing if it can be modified as per the above requirement? Also why is
there a "$" character in constant "sCurrencyFormat$"?

*****
Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2)
Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub
*****

Thanks.
--
tb

Also why is there a "$" character in constant "sCurrencyFormat$"?

The Option Explicit statement enforces variable declaration and VBA 'best
practice' is to turn this on in the 'General' tab of the VBE Preferences
dialog.

The NumberFormat definitions are passed as String values. The $ character is
the Type symbol for 'String'. I have Lou Gehrig's and so I like to save typing
when possible; -using the VB type symbols helps with that!

The alternate syntax is below the line (as a comment)...

*****
Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"
' Const sCurrencyFormat As String = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells

With crng
'If cell number format is Currency AND cell not empty
If .NumberFormat = sCurrencyFormat And Len(crng) 0 Then _
crng.Value = WorksheetFunction.Round(crng * (1 + rng), 2)
End With 'crng

Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub
*****

Thanks.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Your macro worked just fine. Thanks, Garry!

--
tb

On 1/6/2018 at 12:25:03 PM Claus Busch wrote:

Hi,

Am Fri, 5 Jan 2018 14:13:57 +0000 (UTC) schrieb tb:

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.

try:

Sub Test()
Dim wsh As Worksheet
Dim rngc As Range, c As Range
Dim factor As Double
Dim strFormat As String

strFormat = """$""#,##0.00_);(""$""#,##0.00)"

For Each wsh In Worksheets
With wsh
Set c = .UsedRange.Find("pctChange", LookIn:=xlValues)
If Not c Is Nothing Then factor = 1 + c.Offset(1, 0)
If c Is Nothing Or factor = 1 Then GoTo Skip
For Each rngc In .UsedRange
If rngc.NumberFormat = strFormat And IsNumeric(rngc) Then
rngc = Round(rngc * factor, 2)
End If
Next
End With
Skip:
Next
End Sub


Regards
Claus B.

Claus, I was not able to use your macro. Basically, nothing happes
when I launch it. Is there something else that I need to do in order
to successfully run your macro?

Garry's macro worked for me, but I am interested in learning more about
macros and maybe I'm doing something wrong with yours...

--
tb

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Your macro worked just fine. Thanks, Garry!

You're welcome; -glad to help and I appreciate the feedback!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion