# Macro to calculate % and Number (Vice versa)

#### jillibillijames

##### Board Regular
Hi,

I have two columns with "Amount" and "% increased". If I change the number in the cell containing amount the "%" should change and if I change "%", the amount should automatically calculate basing on the change in "%"

Regards
James

<colgroup><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi James,

Trying pasting the code below in the "Workbook_SheetChange" event. It assumes the "amounts" are in column 1 and the "percentage" is in column 2 (columns A & B). You must change the column numbers in the code if you have something different.

Very confusing operation. You may have to swap the dividend / divisor to make it behave the way you want.

Hope it helps.

Gary

In the "Workbook_SheetChange" event:
Code:
``````Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count <> 1 Then Exit Sub

Application.EnableEvents = False

If Target.Column = 1 Then ' Number of column containing "Amount"

On Error Resume Next 'Trap division by zero

Target.Offset(0, 1).Value = Target.Offset(0, 1).Value / Target.Value

If Err Then
Err.Clear
On Error GoTo 0
Application.EnableEvents = True
Exit Sub
End If

End If

If Target.Column = 2 Then ' Number of column containing Percentage

Target.Offset(0, -1).Value = Target.Value * Target.Offset(0, -1).Value

End If

Application.EnableEvents = True

End Sub``````

Hi Gary,

Thanks for the reply and apologies for the delay in reply. Let me brief what exactly I am looking for. Below is the example.

 Current Amt Change in Amt % Change 10000 5000 50% 28000 6000 21%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Taking "Current Amt" as base if i enter the number in "Change in Amt" percentage should get calculated or vice versa ...If I enter the number in "% Change", "Change in Amt" has to be calculated.

Regards
James

Try this:-
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Columns("B:C")) Is Nothing Then
With Application
If .IsNumber(Cells(Target.Row, 1)) And .IsNumber(Cells(Target.Row, 2)) And .IsNumber(Cells(Target.Row, 3)) Then
Select Case Target.Column
Case 3: Target.Offset(, -1) = Target.Offset(, -2) * Target
Case 2: Target.Offset(, 1) = Target / Target.Offset(, -1)
End Select
End If
End With
End If
Application.EnableEvents = True
End Sub``````

Hi MickG,

I have tried but it did not work. I dont understand where i went wrong if it is working for you.

Regards
James

Thank you Mick

Replies
9
Views
160
Replies
1
Views
282
Replies
4
Views
266
Replies
1
Views
175
Replies
0
Views
198

1,202,902
Messages
6,052,451
Members
444,582
Latest member
Scramble

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back