Copy value of Cell to the right - thought I had it

[netrixuser]

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

Status	Alert ID
CLOSED	1
NEW	2
CLOSED	3
CLOSED	4
CLOSED	5
NEW	6
NEW	7
NEW	8

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

 

[JoeMo]

On which worksheet is the data you posted: "Scratch Sheet" or "Current Alerts", and which sheet is active when you run the code?

 

[JustJohn91]

Try this, all i Did was identify the row when CL = "CLOSED".

Sub DeleteIDRow()

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
                ID = Cells(CL.Row, 2)
                
                ws1.Activate
                ws1.Columns("A:A").Select
               
                DeleteRow = Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False).Row
                           
                ActiveSheet.Rows(DeleteRow).Delete
                ws.Activate
            End If
           
        Next CL
    Next rw
End Sub

 

[NoSparks]

Another possibility

Sub TryThis()

Dim statRng As Range, cel As Range, fndAlert As Range

'range on current status
With Sheets("Current Alerts")
    Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each cel In statRng
    If cel.Value = "CLOSED" Then
        'Debug.Print cel.Offset(, 1).Value
        With Sheets("Scratch Sheet")
            Set fndAlert = .Range("A:A").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)
            If Not fndAlert Is Nothing Then
                fndAlert.EntireRow.Delete
            End If
        End With
    End If
Next cel

End Sub

 

[netrixuser]

On which worksheet is the data you posted: "Scratch Sheet" or "Current Alerts", and which sheet is active when you run the code?

Apologies - Scratch sheet is where the email attachments are temp brought into and this sheet is active when the code runs.
Current Alerts is where the identical alert ID will be and is where I want to delete the rows where the ID matches the Closed ID number

 

[netrixuser]

Try this, all i Did was identify the row when CL = "CLOSED".

Sub DeleteIDRow()

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
                ID = Cells(CL.Row, 2)
               
                ws1.Activate
                ws1.Columns("A:A").Select
              
                DeleteRow = Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False).Row
                          
                ActiveSheet.Rows(DeleteRow).Delete
                ws.Activate
            End If
          
        Next CL
    Next rw
End Sub

I get a Variable not defined for ID when I compile the code. If I DIM ID as either Long, Variant, Integer or Range I then get a Variable not found for the line DeleteRow

 

[netrixuser]

Another possibility

Sub TryThis()

Dim statRng As Range, cel As Range, fndAlert As Range

'range on current status
With Sheets("Current Alerts")
    Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each cel In statRng
    If cel.Value = "CLOSED" Then
        'Debug.Print cel.Offset(, 1).Value
        With Sheets("Scratch Sheet")
            Set fndAlert = .Range("A:A").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)
            If Not fndAlert Is Nothing Then
                fndAlert.EntireRow.Delete
            End If
        End With
    End If
Next cel

End Sub

So, looked promising.
The Scratch Sheet is where the emails attachments come in with the latest alerts - hence When an ID has a status of CLOSED, there will be a current alert with the same ID in the Current Alerts sheet that I want to delete. When I ran you code it did nothing (well, it ran without errors) I swapped the Scratch Sheet and Current Alerts reference around as it made more sense to me - but that did the same - it ran, it went through the loop 4 times for the 4 CLOSED entries but nothing was deleted.

At present - the Scratch Sheet and the Current Alerts sheet are identical, so I expect 4 entries to be removed after he code is ran.

 

[NoSparks]

If the sheets are identical.
It won't matter which sheet is active.

Sub TryThis_v2()

Dim statRng As Range, cel As Range, fndAlert As Range

'range on current status
With Sheets("Scratch Sheet")
    Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each cel In statRng
    If cel.Value = "CLOSED" Then
        'Debug.Print cel.Offset(, 1).Value
        With Sheets("Current Alerts")
            Set fndAlert = .Range("B:B").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)
            If Not fndAlert Is Nothing Then
                fndAlert.EntireRow.Delete
            End If
        End With
    End If
Next cel

End Sub

 

[netrixuser]

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

Status	Alert ID
CLOSED	1
NEW	2
CLOSED	3
CLOSED	4
CLOSED	5
NEW	6
NEW	7
NEW	8

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

If the sheets are identical.
It won't matter which sheet is active.

Sub TryThis_v2()

Dim statRng As Range, cel As Range, fndAlert As Range

'range on current status
With Sheets("Scratch Sheet")
    Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each cel In statRng
    If cel.Value = "CLOSED" Then
        'Debug.Print cel.Offset(, 1).Value
        With Sheets("Current Alerts")
            Set fndAlert = .Range("B:B").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)
            If Not fndAlert Is Nothing Then
                fndAlert.EntireRow.Delete
            End If
        End With
    End If
Next cel

End Sub

I forgot to thank you for your time (and in the other posts above to the other folk - my bad)
This is exactly the same ie. the loop runs four times (as there are four times when the Status column cel = CLOSED) but nothing is deleted, on either sheet.
The debug.print for "cel" is 1,0,0,0 - I assumed the offset would mean it would print the Alerts ID (am I assuming correct here?)

 

[NoSparks]

The debug.print for "cel" is 1,0,0,0 - I assumed the offset would mean it would print the Alerts ID (am I assuming correct here?)

Yes.
All I have to go by is the sample data of your original post,
and it doesn't have any zeros for debug to print so I don't know what you're running the code against but it's not that.