Forum Discussion

Kenneth_Manuel's avatar
Kenneth_Manuel
Copper Contributor
Apr 07, 2023

NEED HELP!! If Cell contain specific text then move row to another sheet

Hi Everyone,

 

Need help on this code: 

 

Sub Status_Flag()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Raw Data").UsedRange.Rows.Count
J = Worksheets("Distribute Flag").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Distribute Flag").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Raw Data").Range("B1:B" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "ETA Not Passed" Or Trim(CStr(xRg(K).Value)) = "Delivery ETA Not Passed" Or Trim(CStr(xRg(K).Value)) = "Schedule Not Passed" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Distribute Flag").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "ETA Not Passed" Or Trim(CStr(xRg(K).Value)) = "Delivery ETA Not Passed" Or Trim(CStr(xRg(K).Value)) = "Schedule Not Passed" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
Channel
End Sub

 

-- Im trying to filter "ETA Not Passed" , "Delivery ETA Not Passed" & "Schedule Not Passed" to another sheet which works. but moves even the cells that contain the word "Schedule" or "Delivery". Do you guys have any Idea on how I can only move the Row if the cell contains the exact text?

excel
Macros and VBA

1 Reply

  • rzaneti's avatar
    rzaneti
    Iron Contributor
    Apr 10, 2023

    Hi Kenneth_Manuel ,

     

    I tried to run some tests with your code and everything seems to be working properly. 

     

    The only thing is that you are applying your second IF statement (the code block 'K = K - 1') after deleting the row with  "ETA Not Passed" , "Delivery ETA Not Passed" & "Schedule Not Passed". I don't know if this is the intention, but I flag it, as you are applying the K decrement to the row below the keywords.

     

    To try to find a solution, you can debug it "step-by-step", by opening the VBA interface, selecting your method and clicking F8 (and then F8 again for each line of code). This approach will basically run your program line by line, so it will be easy to find any unexpected behavior.

     

    Also, if you prefer, you can send us a small sample of your workbook (you can populate it with fake data), as your particular workbook design may be causing this issue. 

Resources