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

netrixuser

Board Regular
Joined
Jan 21, 2019
Messages
77
Office Version
  1. 365
Platform
  1. 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 .....

StatusAlert ID
CLOSED1
NEW2
CLOSED3
CLOSED4
CLOSED5
NEW6
NEW7
NEW8

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
 
Hopefully you didn't see my reply that I posted 10 mins earlier....... if you did just ignore.

Thank you NoSparks - the v2 code is working just as I asked !""

I'll be back with more questions I'm sure but many thanks again.

Regards
Netrix
 
Last edited:
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top