none
Loop formula / VBA code that increments a cell value until match is achieved. RRS feed

  • Question

  • Hi Everyone

    I am needing either an excel formula or a VBA code to help me do the following on my salary spread sheets.

    Notes about the salaries calculation. 

    1. I know the net amount that needs to paid to each member of staff.

    2. I have already done the Tax etc calculations to work backwards to a gross figure which i have to input manually 

    3. each month the staff earn a different gross salary as they get paid based on days worked per month.

    So basically 

    Cell   AD8  = E8  

    E8 is the value that AD8 needs to match to 

    AD8 is the cell the net salary appears after the Tax Calculations

    J8 is the Gross salary cell where i need the formula to increment in values of 0.01 until   AD8 = E8

    I will need to copy the formula down to the next Row for the next staff member / loop to the next row until i have the next match  i.e.  AD9 = E9

    I hope this makes sense and somebody can help me as i am needing to save time on calculating the salaries as i have over 100 employees and it is very time consuming manually adjusting the Gross salary until it matches what the net salary should be after taxes.

    Many thanks

    Jason Edwards


    Sunday, February 8, 2015 8:46 AM

Answers

  • Hi Jason,

    According to the description, you want to set formual untial a cell macthed.

    As far as I know, we can set the forumal for a cell via Range.Formula. The following code example sets the formula for cell A1 on Sheet1.

    Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
    
     

    And to get the matched cell, we can compare the value of a cell with the requirement. This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces the value with 0 (zero).

    For Each c in Worksheets("Sheet1").Range("A1:D10") 
     If c.Value < .001 Then 
     c.Value = 0 
     End If 
    Next c
    

    If you still have the issue about using Excel object model, I sugges that you separate the technial issue from business to get more effecitve response.

    Also if you have issues about using formula, I suggest that you get more effectve response from Excel IT Pro Discussions forum for issues of end-user.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 7:00 AM
    Moderator

All replies

  • Hi Jason.

    I'm a bit confused as to what you are trying to do.  You have a cell, let's call it AD8, that is what the net salary should be after taxes.  And you want to automatically calculate what the gross salary should be to achieve that net salary and put that in cell E8?  Can't you create an in cell formula or VBA function that performs the net to gross calculation?  Is that what you want to do?  If so, I'd need some more information about your tax system.

    If it is a simple percentage of the gross salary (let's call it T.P. for tax percentage) then

    gross = net / (1 - T.P.)

    If it is a more complicated system then can you explain that or if I am completely misunderstanding what you want to do can you help me understand?

    Sunday, February 8, 2015 9:03 PM
  • Hi Kmart92

    The tax is very complicated , with many different deductions  etc. 

    Basically E8  is my Know net amount.

    J8 is the Gross salary figure which is not know.

    K8 up to AC8 are the different tax calculations to get to the net calculated salary figure that is in AD8  (the tax calculations in K8 up to AC8 work off the gross salary amount in J8).

    so basically i have to manually adjust J8 until the value in AD8 will equal E8 that is when i know the Gross salary is now correct and all the tax calculations have worked out correctly. 

    So basically what I am wanting to do  is cut out the manually gross salaries entering of Data in Cell J8  so all i want to do is enter the net salary in E8 and the calculation does the rest to arrive at the correct Gross Salary amount

    I hope that makes more sense. 


    Many thanks.

    Jason 

    Monday, February 9, 2015 5:31 AM
  • Hi Jason,

    According to the description, you want to set formual untial a cell macthed.

    As far as I know, we can set the forumal for a cell via Range.Formula. The following code example sets the formula for cell A1 on Sheet1.

    Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
    
     

    And to get the matched cell, we can compare the value of a cell with the requirement. This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces the value with 0 (zero).

    For Each c in Worksheets("Sheet1").Range("A1:D10") 
     If c.Value < .001 Then 
     c.Value = 0 
     End If 
    Next c
    

    If you still have the issue about using Excel object model, I sugges that you separate the technial issue from business to get more effecitve response.

    Also if you have issues about using formula, I suggest that you get more effectve response from Excel IT Pro Discussions forum for issues of end-user.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 7:00 AM
    Moderator