ganu learner
New Member
- Joined
- Dec 31, 2019
- Messages
- 47
- Office Version
- 2013
- Platform
- Windows
All dear friends please update my script with one another condition. Script is at the bottom of page.
According to script
if Cells(currow, "B") > Cells(currow + 1, "B") Then
col = "E"
ElseIf Cells(currow, "B") < Cells(currow + 1, "B") Then
col = "F"
Else
col = "G"
WorksheetFunction.Sum(Range("G5:G" & currow))
Now I want to add one another condition on it.
if Cells(currow, "B") = Cells(currow + 1, "B")
I explain with example
Suppose B10=B9
Then B10 compare with previous cells B8,B7..... more till then it's valve is higher or lower with B cells.
Suppose B6. (B10<B6). is higher then the value of C9 is add with sum of F4 and sum of match condition shown in B4.
Suppose B6 (B10>B6) Is lower then the value of C9 is add with sum of E4 and sum of match condition shown in C4.
According to script
if Cells(currow, "B") > Cells(currow + 1, "B") Then
col = "E"
ElseIf Cells(currow, "B") < Cells(currow + 1, "B") Then
col = "F"
Else
col = "G"
WorksheetFunction.Sum(Range("G5:G" & currow))
Now I want to add one another condition on it.
if Cells(currow, "B") = Cells(currow + 1, "B")
I explain with example
Suppose B10=B9
Then B10 compare with previous cells B8,B7..... more till then it's valve is higher or lower with B cells.
Suppose B6. (B10<B6). is higher then the value of C9 is add with sum of F4 and sum of match condition shown in B4.
Suppose B6 (B10>B6) Is lower then the value of C9 is add with sum of E4 and sum of match condition shown in C4.
VBA Code:
Private Sub Worksheet_Calculate()
Dim capturerow As Long, currow As Long, col As String
On Error GoTo handerror
Application.EnableEvents = False
capturerow = 2
currow = Range("A65536").End(xlUp).Row
If currow < 5 Then currow = 5
Cells(currow + 1, 1) = Cells(capturerow, 1)
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 3) = Cells(capturerow, 3)
Cells(currow + 1, 4) = Cells(capturerow, 4)
If currow > 5 Then
If Cells(currow, "B") > Cells(currow + 1, "B") Then
col = "E"
ElseIf Cells(currow, "B") < Cells(currow + 1, "B") Then
col = "F"
Else
col = "G"
End If
Cells(currow, col) = Cells(currow + 1, "C") - Cells(currow, "C")
End If
Range("E4").Value = WorksheetFunction.Sum(Range("E5:E" & currow))
Range("F4").Value = WorksheetFunction.Sum(Range("F5:F" & currow))
Range("G4").Value = WorksheetFunction.Sum(Range("G5:G" & currow))
handerror:
Application.EnableEvents = True
End Sub
Last edited by a moderator: