do something here with do until loop

ganu learner

New Member
Joined
Dec 31, 2019
Messages
43
Office Version
  1. 2013
Platform
  1. Windows
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"
' do somthing here
' not go to col = "G"
' here I want
'if cells(curruow, "B") = cells(currow + "B") then
'do until
'cells(currow, "B") compare with previous cells of b coloumn until <,>
'if > then
' col = "E"
' else
' col = "F"
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ganu learner

New Member
Joined
Dec 31, 2019
Messages
43
Office Version
  1. 2013
Platform
  1. Windows
Hello,
can you give more detail on your question?


Res Sir, thanks to reply me
this is my real vba script below here i bold and underline my script where i want to change some condition.



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

' do something here

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


' this below script is not useful for modify condition


Range("G4").Value = WorksheetFunction.Sum(Range("G5:G" & currow))

handerror:
Application.EnableEvents = True
End Sub

Sir according to above script
If cells(currow, "B") = cells(currow + 1, "B") then
col = "G"
but I want in this condition some change like do until condition here
i want
If cells(currow, "B") = cells(currow + 1, "B") then


cells(currow, "B") compare with its previous cell until it is bigger or smaller.

now if cells(currow, "B") is > of any of previous cells than
col = "E"

if cells(currow, "B") is <of any of previous cells than
col = "F"
 

Watch MrExcel Video

Forum statistics

Threads
1,118,988
Messages
5,575,393
Members
412,660
Latest member
eddiewest98
Top