Solved How to expand codes in a mathematical operation

July 22, 2014 at 07:49:08
Specs: Windows 7
How do I...You have been helping me with my problem and I want to do more on my spreadsheet. I have a large spreadsheet with many rows and columns that need to be multiplied by the percentage I have put in D1.
Listed below is solving a number in D6 but I have numbers in D6 thru D11; F6 thru F11; H6 thru H11; and I6:I11. To complicate things the data in the H and I rows are rounded via ROUNDUP. I have not been able to expand my Macro to do what I have listed.
Would you please assist me again? I think if I can get the hang of doing these rows and columns I can do the remaining of the spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
    'disable events
    Application.EnableEvents = False
    'multiply D1 value to D6 and save in D6
    Range("D6").Value = Range("D6").Value + Range("D6").Value * Range("D1").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub


See More: How to expand codes in a mathematical operation

Report •


✔ Best Answer
July 24, 2014 at 08:44:41
If you are going to be using this macro, you should understand a little bit about how it works, so that you can make changes to it as required.

You will note that I used the ROUNDUP function in the macro as follows:

     myCell.Value = _
          WorksheetFunction.RoundUp(myCell.Value + _
                                    myCell.Value * _
                                    Range("D1").Value, 2)

As I'm sure you know, the ROUNDUP function requires 2 arguments:

ROUNDUP( number, digits )

Per the code...

Number --> myCell.Value + myCell.Value * Range("D1").Value

Digits --> 2

Since you don't want to see the 2 decimal digits in your results, I'll leave it up to you to determine what the Digits argument should be. Hint: It's no different than if you were using the ROUNDUP function in a worksheet cell.

Perhaps it would help if you read the following tutorial, which offers some tips and tricks that might help you understand how the macro works. Note: Since the code you are using is a Worksheet_Change macro, you can't really use the steps outlined in the tutorial until you turn it into a "regular macro". That is easily done by simply changing the first line from:

Private Sub Worksheet_Change(ByVal Target As Range)

to

Sub myTestMacro()

Here is a link to the tutorial:

http://www.computing.net/howtos/sho...

message edited by DerbyDad03



#1
July 22, 2014 at 10:57:20
Before I offer a suggestion for your latest question, can you tell me what you are trying to do in the error handler section?

As far as I can tell the code will never enter that section because there is no On Error instruction to direct the code to the error handler. Typically you would have something like this at the beginning of the code so that VBA knows what to do if it encounters an error:

On Error GoTo ErrHnd

Without that, the error will occur and the code will stop.

Can you explain why that error handler section is in the code?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 22, 2014 at 13:48:31
The 'error handler data was with me at the beginning of my inquires. If you could look back at the June 26 2014 "Compile Error from VBA Editor" and the July 16 2014 "How to Code A Specific Mathematical Operation in VBA" you will how I submitted the data.

If I delete the data beginning 'error handler down to the End Sub then my spreadsheet won't work anymore.

I don't really understand programming MACRO data and have stumbled along to get this far.

If you could lead me forward I would appreciate it.


Report •

#3
July 22, 2014 at 15:37:31
Try this code.

2 Notes:

1 - You didn't say what your num_digits argument for the ROUNDUP function should be so I used 2.

2 - I set your Error Handler to Resume Next. What that will do is continue with the calculations even if there is an invalid entry in a cell. For example, if D9 contained "Fred" the code would throw up an error when it tried to do the calculation and not do any other calculations. By using On Error Resume Next, it will basically skip any cell where the calculation fails. In other words it will Resume with the Next instruction.

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
  If Target.Address = "$D$1" Then
   'disable events
     Application.EnableEvents = False
   'multiply D6:D11, F6:F11 by D1, save in original cell
      For Each myCell In Range("D6:D11,F6:F11")
        myCell.Value = myCell.Value + myCell.Value * Range("D1").Value
      Next
   'multiply H6:H11, I6:I11 by D1, ROUNDUP to 2 digits, save in original cell
      For Each myCell In Range("H6:H11,I6:I11")
        myCell.Value = _
          WorksheetFunction.RoundUp(myCell.Value + _
                                    myCell.Value * _
                                    Range("D1").Value, 2)
      Next
   'reenable events
      Application.EnableEvents = True
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 24, 2014 at 04:51:40
Thank you.

On the Round Up issue. One of the numbers involved was originally 55.00 and when I used your data it changed to 56.38 which would have been the 2.5% increase. Would you please look at the issue again and see what it would take to round the 56.38 to 57.00?


Report •

#5
July 24, 2014 at 08:44:41
✔ Best Answer
If you are going to be using this macro, you should understand a little bit about how it works, so that you can make changes to it as required.

You will note that I used the ROUNDUP function in the macro as follows:

     myCell.Value = _
          WorksheetFunction.RoundUp(myCell.Value + _
                                    myCell.Value * _
                                    Range("D1").Value, 2)

As I'm sure you know, the ROUNDUP function requires 2 arguments:

ROUNDUP( number, digits )

Per the code...

Number --> myCell.Value + myCell.Value * Range("D1").Value

Digits --> 2

Since you don't want to see the 2 decimal digits in your results, I'll leave it up to you to determine what the Digits argument should be. Hint: It's no different than if you were using the ROUNDUP function in a worksheet cell.

Perhaps it would help if you read the following tutorial, which offers some tips and tricks that might help you understand how the macro works. Note: Since the code you are using is a Worksheet_Change macro, you can't really use the steps outlined in the tutorial until you turn it into a "regular macro". That is easily done by simply changing the first line from:

Private Sub Worksheet_Change(ByVal Target As Range)

to

Sub myTestMacro()

Here is a link to the tutorial:

http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Report •

#6
July 26, 2014 at 05:45:59
Thank you for all your help.

Report •


Ask Question