calculation for all the rows

  • Dear Team,

    I am writing code for calculating my values. My code is

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Sheets("Master data").Range("E4").Value = (250 - (Sheets("Master data").Range("T4").Value - Sheets("Master data").Range("L4").Value))
    End Sub

    This calculation to be continued for all the rows bellow from E4.

    For example

    E4= 250-(T4-L4)

    E5= 250-(T5-L5)

    E6 = 250-(T5-L5)

    Like that this formula to be applied automatically for all the rows .

    can any one help me to how to do this

  • I not exactly sure how you'd need to set this up to work for what you're wanting to do but using something like:

    Maybe this can put you on the right path or one of the experts can chime in and tweak this to work for you.

  • An even easier way.

  • Hi ,

    Your code is working. But where the row is in blank on T column then this formula should not update. Once the value is entered on T column then only this formula to be update the value on E. Can you pls change it

  • Wait, i think i understand. To put it into simple terms, if column " T " is empty, skip that cell (no update) until a value is entered into column " T " then calculate it. Does that sound about right?

  • OK, i think this is what you are wanting. Try this out and see if it works as you want it to.

  • Is it possible to use the condition before execute the calculation

    I had changed your code like this

    But i want use condition for J4:J

     If .Range("C4:C" & LRow).Value = "ZL50GV(ARAI)" Or .Range("C4:C" & LRow).Value = "LW300FV(ARAI)" Then
     .Range("J4:J" & LRow).Formula = "=2000 - (T4-Q4)"
     .Range("J4:J" & LRow).Formula = "=1000 - (T4-Q4)"
     End If

    With this above code i am getting "Type mismatch error".

    I am attaching my file here for your reference. Can you please check and confirm

  • I think this is what you're talking about. Try this and see if it works for you. Please enter some data and see if the figures work out.

  • In the test book you sent me, the values in the "C" column are "ZL50GN(ARAI)"and"LW300FN(ARAI)" respectively. The values you have set in the code are "ZL50GV(ARAI)"and"LW300FV(ARAI)". ( Notice the "V" at the end of the number before the (ARAI) and an "N" in the same position in the the sheet data.)

    Could this be the problem? This is why I wanted you to enter the data to check the values. Please check to be sure you are entering values correctly and let me know if this was or was not the issue.

  • Hi ,

    The problem is not "V" & "N". I had checked with both of this.

    The problem is this calculating with first condition (what ever the value is entered) only and this is considering second condition ( else condition)

  • Hi ,

    I found that if the machine model is "LW300FV(ARAI)" then entire column changed to "=2000 - (T4-Q4)" other wise entire column goes to "=1000 - (T4-Q4)".

    But when the machine model is "LW300FV(ARAI)" or "ZL50GV(ARAI)" the particular row only change to "=2000 - (T4-Q4)" other wise it should be in "=1000 - (T4-Q4)".

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!