Macro to calculate % and Number (Vice versa)

[jillibillijames]

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 "%"

Please help and thank you in advance

Regards
James

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

 

[Gary McMaster]

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:

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

 

[jillibillijames]

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.

Thanks in advance..

Regards
James

 

[MickG]

Try this:-

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

 

[jillibillijames]

Hi MickG,

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

Thank you for your time.

Regards
James

 

[MickG]

Have a look at this example ( this code is slightly change from the previous.
https://app.box.com/s/dxkcey5x15acb5gaej3m5ffsat26um8i

 

[jillibillijames]

Thank you Mick