VBA, Excel - Compare rows for multiple conditions, copy and add the result to a new sheet
In my Excel sheet, I have several values that I need to compare and summarize if certain criteria are met.
The worksheet contains the following information:
Name (A), Date (B), Business Hours (C), Other Information (DH).
Through VBA, I want to check if the hours work exceeds the value "10". If so, then the code must be compared if the Name in the previous line is equal to the Name in the current AND Date of both lines are equal to each other.
If all of these conditions are true, then the Hours of Operation should be summarized and the result should be copied to worksheet 2. You also need to copy the necessary information such as name, date and other information.
At the moment I have tried this:
Sub check_Click()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, p As Long
Set s1 = Sheets(1)
Set s2 = Sheets(2)
N = s1.Cells(s1.Rows.Count, "C").End(xlUp).Row
p = 1
For i = 1 To N
If IsNumeric(s1.Range("C" & i)) And s1.Cells(i, "C").Value < 10 Then
Next i
ElseIf s1.Cells(i, "B").Value = s1.Cells(i - 1, "B").Value And s1.Cells(i, "A").Value = s1.Cells(i - 1, "A").Value Then
s1.Range(Cells(i, "A"), Cells(i, "C")).Copy s2.Cells(p + 5, 1)
End If
End Sub
As you can see, the code doesn't work - bad luck.
I hope someone can shed some light.
The hardest part is to compare the previous line and sum up the hours.
Thank you in advance
source to share
The code is wrong. Next i
It can not be used inside If
... Then
.
Due to the lack of continuation in VBA, you also need to change the condition (or use Goto
, but this is not my preferred solution):
Sub check_Click()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, p As Long
Set s1 = Sheets(1)
Set s2 = Sheets(2)
N = s1.Cells(s1.Rows.Count, "C").End(xlUp).Row
p = 1
For i = 1 To N
If IsNumeric(s1.Range("C" & i)) And s1.Cells(i, "C").Value >= 10 Then
If s1.Cells(i, "B").Value = s1.Cells(i - 1, "B").Value And s1.Cells(i, "A").Value = s1.Cells(i - 1, "A").Value Then
s1.Range(Cells(i, "A"), Cells(i, "C")).Copy s2.Cells(p + 5, 1)
End If
End If
Next i
End Sub
EDIT:
Since the values are compared to the previous line, the loops are required to for
start at 2.
Sub check_Click()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, p As Long
Set s1 = Sheets(1)
Set s2 = Sheets(2)
N = s1.Cells(s1.Rows.Count, "C").End(xlUp).Row
p = 1
For i = 2 To N ' Iterate from second row
If IsNumeric(s1.Range("C" & i)) And s1.Cells(i, "C").Value >= 10 Then
If s1.Cells(i, "B").Value = s1.Cells(i - 1, "B").Value And s1.Cells(i, "A").Value = s1.Cells(i - 1, "A").Value Then
s1.Range(Cells(i, "A"), Cells(i, "C")).Copy s2.Cells(p + 5, 1)
End If
End If
Next i
End Sub
source to share
Yours Next i
is in the wrong place. This should be after all operators If
.
I think the value comparison is correct.
If you are having trouble copying the clock, just copy the entire row to sheet2 first, then separately update the clock-processed cell with something like this:
Worksheets("sheet2").Cells(i,3).Value = Cells(i,3).Value + Cells(i-1,4).Value
Replace with correct cell coordinates of course.
source to share