Dear Experts,



I need to design a macro w.r.t Salary Structure in excel as per following requirement.

Current Salary Structure is follows:-


S.No. Category Emp. Name Basic HRA Conv. Med. CCA SPA Take Home Bonus EPF ESIC CTC
1 1 RK 24,115 12,058 7,235 - 7,235 34,392 85,035 4823 2894 0 92,752
2 2 MSR 8,200 4,100 2,460 1,640 2,119 - 18,519 1640 984 787 21,930
3 2 DS 8,200 4,100 2,460 1,640 418 - 16,818 1640 984 715 20,157
4 1 NS 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
5 2 RA 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
6 1 EB 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738

I wish to keep new Salary Structure as follows:-

% Increment New CTC Gross Increase New Basic New HRA New Conv. New Med. New CCA New SPA New Take Home New Bonus New EPF New ESIC Total CTC
5% 97,390 4,638 25,321 12,661 7,596 5,064 7,596 31,048 89,287 5,064 3,039 - 97,390








Following should be the basis of Calculations: -
New CTC
"CTC" + "CTC"*%i Increment

Gross Increase
CTC - New CTC

New Basic
IF Category = 1 Then Max of 9200 OR 26% of New CTC
IF Category = 2 Then Max of 8200 OR 26% of New CTC
IF Category = 3 Then Max of 7800 OR 26% of New CTC

New HRA
IF((New CTC - (New Basic +New Bonus+New EPF + New ESIC))>0
then
Min of ((New CTC - (New Basic + New Bonus+New EPF + New ESIC), 50% of New Basic)

New Conv.
IF((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC))>0
then
Min of ((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC), 30% of New Basic)

New Med.
IF((New CTC - (New Basic + New HRA+New Conv. + New Bonus+New EPF + New ESIC))>0
then
Min of ((New CTC - (New Basic + New HRA+New Conv. +New Bonus+New EPF + New ESIC), 20% of New Basic)

New CCA
IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New Bonus+New EPF + New ESIC))>0
then
Min of ((New CTC - (New Basic + New HRA+New Conv. + New Med. +New Bonus+New EPF + New ESIC), 30% of New Basic)




New SPA
IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA + New Bonus+New EPF + New ESIC))>0
then
New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA+New Bonus+New EPF + New ESIC)


New Take Home
Sum of New Basic + New HRA + New Conv. + New Med. + New CCA + New SPA

New Bonus
20% of New Basic

New EPF
12% of New Basic

New ESIC
IF "New Take Home" < 21000
Then
"New Take Home"*4.25%
Else
0

Total CTC
Sum of New Take Home + New Bonus + New EPF + New ESIC

While applying formulas in excel it is giving circular error therefore need to have a macro. I wish of to enter only the % of Increment and the succeeded columns should get calculated automatically as per above logics specified. The Macro should get clicked/executed as soon as I enter/change the % of increment.
Hope I have clairified my requirement. Thanks for your cooperation in advance.

Attached Files

1-Sample Work Sheet for Macro Forums.xlsx (16.6 KB, 15 views)

Hi and welcome
please post a sample sheet with some data and expected results ( click Go advanced - Manage attachments)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for your quick response. File uploaded in original query.

The problem is, that calculation p.e. of NewHRA contains reference to NewESIC, which contains reference to NewTakeHome, which contains reference to NewHRA.

So you are here like baron Von Münchausen who is trying to pull himself out of bog pulling his own hair. You can't calculate NewHRA before you know the value of NewHRA!

Somehow I can't edit my own post. I wanted to add, it is moot are you calculating new salaries using formulas or VBA - the problem is basic.

Dear Arvilaanemets
I know the problem is there that’s why I am unable to implement excel’s standard formulas but I believe this can be achieved using macros. We take the simplest example as follows wherein the NewCTC is Rs. 15793
New CTC New Basic New HRA New Total as per Salary Sheet New Bonus New EPF New ESIC Total CTC
15,793 9,200 3,125 12,325 1,840 1,104 524 15,793

Since the following fields can be calculated straight forwardly therefore we will calculate them first
New Basic (For Category 1)
IF Category = 1 Then Max of 9200 OR 26% of New CTC
IF Category = 2 Then Max of 8200 OR 26% of New CTC
IF Category = 3 Then Max of 7800 OR 26% of New CTC
Caculated as Rs. 9200/-


New Bonus
20% of New Basic
Calculated as Rs. 1840/-


New EPF
12% of New Basic
Calcuated as Rs. 1104/-


Now there are 2 fields left (New HRA and New ESIC) which are creating circular references and are posing difficulties in calculation. We know that the total of all the above fields is Rs. 12,144/- and the HRA & ESIC to be calculated in such a way that in totality they should match the CTC value i.e. Rs. 15793/-. In above case the value of HRA is Rs. 3125/- and value of ESIC is Rs. 524/- so that in totality the CTC is coming out to be Rs. 15793/-

If we can insert the calculation of NewHRA in a loop (by using various values) and thereafter we calculate the value of ESIC within loop only and check the total of all the calculated fields with NewCTC. If it matches we freeze the values of HRA and ESIC and exit the loop but if it doesnot in that case we again go to the start of the loop and again follow the above steps within loop untill we meet the required condition.

if we are able to calculate the above values accurately we can consider remaining 5 cases in similar way.

Hope I am clear and not mistaken. Please advice.

can somebody give me a macro only for calculating basic, hra, bonus and pf atleast?

I have achieved the desired results with following MACRO for the simplest case i.e. it is calculating Basic, HRA, Bonus, EPF, ESIC. I need to refine this code (make it more simple and efficient) and apply the same logic for calculating Conv., Med., CCA and SPA Values. There is one more problem, this code only works when i make changes in cell O8 (increment %) and press tab. if i use left, upper or down arrow the code doesnot work. the code should work as soon as there is a change in cell O8. Can somebody help me in this regard?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$8" Then



Dim NewCTC As Double
Dim GrossIncrease As Integer
Dim NewBasic As Double
Dim NewBonus As Double
Dim EPF As Double
Dim FinalCTC As Double

Dim TakeHome As Double
FinalCTC = 0

NewCTC = ActiveCell.Previous.Offset(0, -1).Value + (ActiveCell.Previous.Offset(0, -1).Value) * ActiveCell.Previous.Value
GrossIncrease = NewCTC - ActiveCell.Previous.Offset(0, -1).Value
ActiveCell.Previous.Offset(0, 1).Value = NewCTC
ActiveCell.Previous.Offset(0, 2).Value = GrossIncrease

'New Basic Start
Select Case ActiveCell.Previous.Offset(0, -13).Value
Case 1
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 9200)
Case 2
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 8200)
Case 3
NewBasic = WorksheetFunction.Max(NewCTC * 0.26, 7800)
End Select
ActiveCell.Previous.Offset(0, 3).Value = NewBasic
'New Basic End


'New Bonus Start
NewBonus = NewBasic * 0.2
ActiveCell.Previous.Offset(0, 10).Value = NewBonus
'New Bonus End

'New EPF Start
NewEPF = NewBasic * 0.12
ActiveCell.Previous.Offset(0, 11).Value = NewEPF
'New EPF End


'FOR Freezing HRA Value - New HRA+ESIC Start
NewHRA = NewBasic * 0.5
NewTakeHome = NewBasic + NewHRA
If NewTakeHome < 21000 Then
NewESIC = NewTakeHome * 0.0425
Else
NewESIC = 0
End If
FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC
If FinalCTC > NewCTC Then
Do While (Round(FinalCTC, 0) <> Round(NewCTC, 0))
NewHRA = NewHRA - 1
NewTakeHome = NewBasic + NewHRA
If NewTakeHome < 21000 Then
NewESIC = NewTakeHome * 0.0425
Else
NewESIC = 0
End If
FinalCTC = NewBasic + NewHRA + NewBonus + NewEPF + NewESIC
If NewHRA = 0 Then
Exit Do
End If
Loop
End If
ActiveCell.Previous.Offset(0, 4).Value = Round(NewHRA, 0)
ActiveCell.Previous.Offset(0, 12).Value = Round(NewESIC, 0)
ActiveCell.Previous.Offset(0, 13).Value = Round(FinalCTC, 0)
'New HRA+ESIC End






End If
End Sub

Start by Using Option Explicit and declare the variables you use.

When using Worksheet_Change(ByVal Target As Range) use Target.
You think what you've got works because you changed the value in cell O8 and then the Tab key took you to P8 where ActiveCell.Previous happens to be the Target.
That's not the case when you use the left, upper or down arrow.
Replace all those ActiveCell.Previous with Target.

Writing values to the sheet will have this procedure calling itself over and over.
Look into disabling and re-enabling Events to prevent this.

Have a read of this.

Thanks for your valuable advice. I will try to implement your suggestion but can you please elaborate your following suggestion:-

"Writing values to the sheet will have this procedure calling itself over and over.
Look into disabling and re-enabling Events to prevent this."

I will definitely take care for the suggestions related to cross posting in future.