Solved How to code a specific mathematical operation in VBA

July 16, 2014 at 06:16:19
Specs: Windows 7
I am submitting a Macro that does not work like I need.

If Target.Address = "$B$1" Then
    'disable events
    Application.EnableEvents = False
    'multiply B1 value to A1 and save in A1
    Range("A1").Value = Range("A1").Value * Range("B1").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
'reenable events
Application.EnableEvents = True
End Sub

What I need to do is be able to have the number in B1 multiply the number in A1, and the result appear in A1.
It seems as what it does now is take the number in A1, multiply by the number in B1 and the results that appear in A1 is what the markup was and I needed it to add that markup to the original number in A1 and display the new result.
Example is am working with: the number in A1 is 222.44. The markup in B1 is 2.5%. The number I get right now in A1 is 5.56 and I want the 5.56 added to 222.44 and I need the result in A1 to be 228.00.
If I can get that done, when the next mark up comes I can change B1 and it will work off the 228.00 in A1 and a new price will appear in A1 depending on what the mark up is.
Any help will be appreciated.

See More: How to code a specific mathematical operation in VBA

July 16, 2014 at 07:09:56
✔ Best Answer
Performing mathematical operations in VBA is very similar to performing mathematical operations in a spreadsheet cell. The formulas are the same, it's just the syntax that is different. Of course, the advantage of using VBA is that you can replace the original value in the cell with the result of the formula without the dreaded circular reference error.

There are (at least) 2 formulas for increasing a value by a specific percentage:

=original value + (original value * percentage)


In VBA, that looks like this:

Range("A1").Value = Range("A1").Value + Range("A1").Value * Range("B1").Value


=original value * (1 + percentage)


In VBA that looks like this:

Range("A1").Value = Range("A1").Value * (1 + Range("B1").Value)

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

Report •

July 17, 2014 at 07:01:05
Thank you so much for solving my situation. It works!!

This is a Edit to my prior post. I used your first suggestion which worked.
Do I need to start a NEW THREAD to ask this question? Please advise.

I have a spread sheet full of numbers and need to multiply more than just A1. For instance I have numbers in A1 thru A9, A12 thru A16 and more in C1 thru C9 etc, etc. I have tried to make these changes in just the A1 thru A9 to start off with and it does not work. This is what I did:

'multiply B1 value to A1:A9, A12:A16, C1:C9 and save in A1:A9,A12:A16,C1:C9
Range("A1:A9,A12:A16,C1:C9").Value = Range("A1:A9,A12:A16,C1:C9").Value + Range("A1:A9,A12:A16,C1:C9").Value * Range("B1").Value

message edited by nellb

Report •
Related Solutions

Ask Question