Need Macro...

[tb]

I am using Microsoft Office 2007, 32-bit. I know absolutely nothing
about macros, so, I am hoping that somebody in this group can help me
out.

I have an Excel workbook with several worksheets in it. Each worksheet
has list prices for some of our products, and my task is to apply an X
% price increase to each cell that has a price in it.

The problem is that list prices are in the form of complex tables, thus
it is hard and time consuming to create a formula in some blank column
of each worksheet that calculates the new prices and then copy/paste
the results over to appropriate tables' cells.

What I would like to do is enter the % increase into a cell of the
first worksheet (say cell A1 of Sheet1) and then launch a macro that
will find all the cells that are formatted as currency (in each
worksheet) and apply the % increase.

The only details to keep in mind when writing the macro are:
* The X % percentage increase is across the board (i.e. all prices in
all worksheets will get the same % increase);
* Rounding should be to two decimals;
* If a cell formatted as currency is blank (empty), then that cell
needs to stay blank.

Can anyone please help me?

 

[Claus Busch]

Hi,

Am Mon, 8 Dec 2014 23:43:15 +0000 (UTC) schrieb tb:

What I would like to do is enter the % increase into a cell of the
first worksheet (say cell A1 of Sheet1) and then launch a macro that
will find all the cells that are formatted as currency (in each
worksheet) and apply the % increase.

try:

Sub Test()
Dim i As Long
Dim rngC As Range

For i = 1 To Sheets.Count
For Each rngC In Sheets(i).UsedRange
If Len(rngC) > 0 And rngC.Style = "Currency" Then
'rngc will increased by 5%
rngC = rngC * 1.05
End If
Next
Next
End Sub


Regards
Claus B.

 

[tb]

try:

Sub Test()
Dim i As Long
Dim rngC As Range

For i = 1 To Sheets.Count
For Each rngC In Sheets(i).UsedRange
If Len(rngC) > 0 And rngC.Style = "Currency" Then
'rngc will increased by 5%
rngC = rngC * 1.05
End If
Next
Next
End Sub


Regards
Claus B.

I am getting "Run-time error 13" and "Type mismatch" errors. I'm not
a macro expert so I don't know how to debug this.

Also, where in your macro does the user enter the desired % increase
and where does it round to two decimals?

 

[Claus Busch]

Hi,

Am Tue, 9 Dec 2014 18:36:06 +0000 (UTC) schrieb tb:

I am getting "Run-time error 13" and "Type mismatch" errors. I'm not
a macro expert so I don't know how to debug this.

Also, where in your macro does the user enter the desired % increase
and where does it round to two decimals?

look to the lines above.
You don't have to round because you have Currency as format.
I don't know why you get these errors because I don't know your data. In
my tests the code worked fine.


Regards
Claus B.

 

[tb]

look to the lines above.
You don't have to round because you have Currency as format.
I don't know why you get these errors because I don't know your data.
In my tests the code worked fine.


Regards
Claus B.

Is there a place where I could post the file or do you have an email
address where I could send it?

Thanks and regards.

 

[Claus Busch]

Hi,

Am Tue, 9 Dec 2014 21:19:22 +0000 (UTC) schrieb tb:

Is there a place where I could post the file or do you have an email
address where I could send it?

you could upload your file to a filehoster like DropBox or into a cloud
line OneDrive.
Or send it to me claus_busch(at)t-online.de


Regards
Claus B.

 

[tb]

Hi,

Am Tue, 9 Dec 2014 21:19:22 +0000 (UTC) schrieb tb:


you could upload your file to a filehoster like DropBox or into a
cloud line OneDrive.
Or send it to me claus_busch(at)t-online.de


Regards
Claus B.

Thanks, Claus. I just sent the Excel file to your email address.
Hopefully you can help me out.
Regards.

 

[tb]

Hi Claus. Messages sent to your email account bounce back, so I am
posting here.

I ran the macro that you created both at work and at home, with Excel
2007 and 2010. But I still get nothing -- no prices are changed!

Just want to make sure one more time that I am doing things right since
I am no macro expert.

1) I made sure that "Enable all macros" is selected in the security
level.
2) I entered the % increase in cell O2 of the ATEX tab.
3) I pressed ALT+F8 and selected "Test" as the macro name.
4) I pressed the Run button.
5) A rotating blue circle appears for a few seconds then it is gone.
6) Waited for prices to be changed but nothing happened after waiting a
long time.

Do you see anything wrong with what I am doing?

Best regards.

 

[Claus Busch]

Hi Tiziano,

Am Fri, 12 Dec 2014 15:21:35 +0000 (UTC) schrieb tb:

1) I made sure that "Enable all macros" is selected in the security
level.
2) I entered the % increase in cell O2 of the ATEX tab.
3) I pressed ALT+F8 and selected "Test" as the macro name.
4) I pressed the Run button.
5) A rotating blue circle appears for a few seconds then it is gone.
6) Waited for prices to be changed but nothing happened after waiting a
long time.

did you use the correct address? Yesterday you mailed me and I answered
you.
I don't know why the macro don't change the prices. Everytime I tested
the macro here it does exactly what you expect.


Regards
Claus B.