calculation for all the rows

elsuji · Aug 27th 2020

Dear Team,

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

Code

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

mycomputerguy-w · Aug 27th 2020

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:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim lastRow As Long, i As Integer, totalofEandL As Double, FinalSum As Double

Set FinalSum = Sheets("Master Data").Range("E4").end(xldown)


lastRow = Range("A5000").End(xlUp).Row

For i = 1 To lastRow
    totalofEandL = totalofEandL + WorksheetFunction.Sum(Range("L" & i & ":T" & i))
Next i
FinalSum = totalofEandL
End With

End Sub

Display More

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

mycomputerguy-w · Aug 27th 2020

An even easier way.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim LRow As Long

    Set ws = ThisWorkbook.Sheets("Master Data")

    With ws
        LRow = .Range("E" & .Rows.Count).End(xlDown).Row

        .Range("E4:E" & LRow).Formula = "=250 - L4-T4"
    End With
End Sub

Display More

elsuji · Aug 28th 2020

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

mycomputerguy-w · Aug 28th 2020

ok, so you want the code to only run when column " T " is updated or changed?

elsuji · Aug 28th 2020

yes

mycomputerguy-w · Aug 28th 2020

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?

elsuji · Aug 28th 2020

yes correct

elsuji · Aug 28th 2020

Because now there is no values updated on L & T. But in E it is updating 250 for all the rows.

mycomputerguy-w · Aug 28th 2020

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

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ws As Worksheet
    Dim LRow As Long
    Set ws = ThisWorkbook.Sheets("Master Data")
    With ws
    LRow = .Range("T" & .Rows.Count).End(xlUp).Row
    
    .Range("E4:E" & LRow).Formula = "=250 - L4-T4"
 End With
End Sub

Display More

elsuji · Aug 28th 2020

Thanks for your support.

The code is working great.

If anything is required i will reply back.

mycomputerguy-w · Aug 28th 2020

Glad i could help. Please don't forget like this thread and mark it as solved if you have no other issues.

good day!

elsuji · Aug 28th 2020

Is it possible to use the condition before execute the calculation

I had changed your code like this

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim ws As Worksheet
 Dim LRow As Long
 Set ws = ThisWorkbook.Sheets("Master Data")
 With ws
 LRow = .Range("T" & .Rows.Count).End(xlUp).Row
 .Range("E4:E" & LRow).Formula = "=250 - (T4-L4)"
 .Range("F4:F" & LRow).Formula = "=600 - (T4-M4)"
 .Range("G4:G" & LRow).Formula = "=1000 - (T4-N4)"
 .Range("H4:H" & LRow).Formula = "=1000 - (T4-O4)"
 .Range("I4:I" & LRow).Formula = "=1000 - (T4-P4)"
 .Range("K4:K" & LRow).Formula = "=2000 - (T4-R4)"
 .Range("J4:J" & LRow).Formula = "=1000 - (T4-Q4)"
 End With
End Sub

Display More

But i want use condition for J4:J

Code

 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)"
 Else
 .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

mycomputerguy-w · Aug 28th 2020

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.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim ws As Worksheet, LRow As Long, DifValue As String
 Set ws = ThisWorkbook.Sheets("Master Data")
 With ws
 LRow = .Range("T" & .Rows.Count).End(xlUp).Row
 .Range("E4:E" & LRow).Formula = "=250 - (T4-L4)"
 .Range("F4:F" & LRow).Formula = "=600 - (T4-M4)"
 .Range("G4:G" & LRow).Formula = "=1000 - (T4-N4)"
 .Range("H4:H" & LRow).Formula = "=1000 - (T4-O4)"
 .Range("I4:I" & LRow).Formula = "=1000 - (T4-P4)"
 .Range("K4:K" & LRow).Formula = "=2000 - (T4-R4)"
 If ws.Cells(LRow, "C").Value = "ZL50GV(ARAI)" Or ws.Cells(LRow, "C").Value = "LW300FV(ARAI)" Then
 .Range("J4:J" & LRow).Formula = "=2000 - (T4-Q4)"
 Else
 .Range("J4:J" & LRow).Formula = "=1000 - (T4-Q4)"
 End If
 
 End With

End Sub

Display More

elsuji · Aug 29th 2020

Hi i cheeked your code. But it is not working. If the condition is same or different, but this is taking only the first condition.

can you please check and confirm

mycomputerguy-w · Aug 30th 2020

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.

elsuji · Aug 30th 2020

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)

elsuji · Aug 30th 2020

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)".

elsuji · Sep 2nd 2020

Hi,

Can you please help me to solve this problem

mycomputerguy-w · Sep 2nd 2020

sorry, ive gotten side tracked on some other projects and Yes, i will work on this and see about getting you a solution soon.

calculation for all the rows

Participate now!

Share