excel_beginer
New Member
- Joined
- Dec 28, 2017
- Messages
- 19
Hi all
I have code below to check balansheet then copy row with conditions
But I thinks this code too long and run slow, could you make this code shorter
Many thank
I have code below to check balansheet then copy row with conditions
But I thinks this code too long and run slow, could you make this code shorter
Many thank
Code:
Sub CheckPS_noibang()Worksheets("result").Range("A20:Z15000").Clear
Application.ScreenUpdating = False
Dim i As Integer, lastrow As Integer, lastrownoibang As Integer
lastrownoibang = Sheets("noibang").Range("B" & Rows.Count).End(xlUp).Row + 1
For i = 2 To lastrownoibang
lastrow = Sheets("Result").Cells(Rows.Count, 2).End(xlUp).Row
With Sheets("noibang")
If (Left(.Cells(i, 3), 1) = 5 Or Left(.Cells(i, 3), 1) = 6 Or Left(.Cells(i, 3), 2) = 47 Or Left(.Cells(i, 3), 3) = 486 Or .Cells(i, 3) = 20 Or .Cells(i, 3) = 21 Or .Cells(i, 3) = 22 Or .Cells(i, 3) = 23 Or .Cells(i, 3) = 24 Or .Cells(i, 3) = 25 Or .Cells(i, 3) = 26 Or .Cells(i, 3) = 27 Or .Cells(i, 3) = 28 Or .Cells(i, 3) = 29 Or .Cells(i, 3) = 30 Or .Cells(i, 3) = 48) And (.Cells(i, 4).Value - .Cells(i, 5).Value) + (.Cells(i, 6).Value - .Cells(i, 7).Value) <> (.Cells(i, 8).Value - .Cells(i, 9).Value) Then
.Rows(i).Font.Color = vbRed
.Rows(i).Copy (Sheets("Result").Rows(lastrow + 1))
ElseIf (Left(.Cells(i, 3), 1) = 1 Or Left(.Cells(i, 3), 1) = 2 Or Left(.Cells(i, 3), 1) = 3 Or Left(.Cells(i, 3), 1) = 8) And (.Cells(i, 3) <> 12 And .Cells(i, 3) <> 13 And .Cells(i, 3) <> 14 And .Cells(i, 3) <> 15 And .Cells(i, 3) <> 16 And .Cells(i, 3) <> 20 And .Cells(i, 3) <> 21 And .Cells(i, 3) <> 22 And .Cells(i, 3) <> 23 And .Cells(i, 3) <> 24 And .Cells(i, 3) <> 25 And .Cells(i, 3) <> 26 And .Cells(i, 3) <> 27 And .Cells(i, 3) <> 28 And .Cells(i, 3) <> 29 And .Cells(i, 3) <> 30 And Left(.Cells(i, 3), 3) <> 149 And Left(.Cells(i, 3), 3) <> 159 And Left(.Cells(i, 3), 3) <> 169 And Left(.Cells(i, 3), 3) <> 209 And Left(.Cells(i, 3), 3) <> 219 And Left(.Cells(i, 3), 3) <> 229 And Left(.Cells(i, 3), 3) <> 239 And Left(.Cells(i, 3), 3) <> 249 And Left(.Cells(i, 3), 3) <> 259 And Left(.Cells(i, 3), 3) <> 269 And Left(.Cells(i, 3), 3) <> 279 And Left(.Cells(i, 3), 3) <> 289 And Left(.Cells(i, 3), 3) <> 299 And Left(.Cells(i, 3), 3) <> 305) Then
If (.Cells(i, 4).Value + .Cells(i, 6).Value - .Cells(i, 7).Value) <> .Cells(i, 8).Value Then
.Rows(i).Font.Color = vbRed
.Rows(i).Copy (Sheets("Result").Range("A" & lastrow + 1))
End If
ElseIf (Left(.Cells(i, 3), 1) = 4 Or Left(.Cells(i, 3), 1) = 7 Or Left(.Cells(i, 3), 3) = 209 Or Left(.Cells(i, 3), 3) = 219 Or Left(.Cells(i, 3), 3) = 229 Or Left(.Cells(i, 3), 3) = 239 Or Left(.Cells(i, 3), 3) = 249 Or Left(.Cells(i, 3), 3) = 259 Or Left(.Cells(i, 3), 3) = 269 Or Left(.Cells(i, 3), 3) = 279 Or Left(.Cells(i, 3), 3) = 289 Or Left(.Cells(i, 3), 3) = 299 Or Left(.Cells(i, 3), 3) = 305) And ((Left(.Cells(i, 3), 2) <> 47) And (Left(.Cells(i, 3), 3) <> 486) And (.Cells(i, 3) <> 48) And (.Cells(i, 5).Value + .Cells(i, 7).Value - .Cells(i, 6).Value) <> .Cells(i, 9).Value) Then
.Rows(i).Font.Color = vbRed
.Rows(i).Copy (Sheets("Result").Rows(lastrow + 1))
End If
End With
Next i
Application.ScreenUpdating = True
End Sub