netrixuser
Board Regular
- Joined
- Jan 21, 2019
- Messages
- 77
- Office Version
- 365
- Platform
- Windows
Hello, below are the first two columns of a report that I get. The status can either be NEW, EXISTING or CLOSED.
For those that are CLOSED I wish to take the Alert ID number - then go to another sheet in the workbook, look for that ID and delete the entire row where that ID resides.
The code below doesn't copy the Alert ID, I used "debug.print d" and I get a 0 for the 4 times that the IF matches. (the code is up to obtaining the ID, I haven't tried to delete the corresponding row yet)
I know this is a simple fix, but I cannot see where my error is .....
Code:
For those that are CLOSED I wish to take the Alert ID number - then go to another sheet in the workbook, look for that ID and delete the entire row where that ID resides.
The code below doesn't copy the Alert ID, I used "debug.print d" and I get a 0 for the 4 times that the IF matches. (the code is up to obtaining the ID, I haven't tried to delete the corresponding row yet)
I know this is a simple fix, but I cannot see where my error is .....
Status | Alert ID |
CLOSED | 1 |
NEW | 2 |
CLOSED | 3 |
CLOSED | 4 |
CLOSED | 5 |
NEW | 6 |
NEW | 7 |
NEW | 8 |
Code:
VBA Code:
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Scratch Sheet")
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Current Alerts")
Dim lastRow As Long
Dim lastRow1 As Long
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
lastRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = ws.Range("A1:A" & lastRow)
Dim i As Integer
Dim d As Long
Dim rw As Range
Dim CL As Range
For Each rw In ws.Range("A1:A11")
For Each CL In rw.Cells
If (CL.Value) = "CLOSED" Then
d = CL.Offset(0, 1).Value
Debug.Print d
End If
Next CL
Next rw
End Sub