VBA Question: I need to modify my if statement using "Find" or "match"

[CLoos]

Below is the code to loop through and move the row. My struggle with this is too be able to find the word "Premium" within a cell never at the same position within a cell. I want to also look for the word "Special" within a cell. I assume I need to do an OR within the IF statement. Do I need to create a variable and use that? It is the equal signs that are confusing me. Thanks for any help.

[Sub Move_Data()
Dim i As Long, LastRow As Long, mydata As String, cutrow As Long

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

Application.ScreenUpdating = True

For i = LastRow To 2 Step -1
mydata = Cells(i, “D”)

If mydata = Range("D3:D5000").Find("Premium", Range("D3"), xlValues, xlWhole, xlByColumns, xlNext).row Then
Cells(i, “D”).EntireRow.Cut
cutrow = Worksheets("DataSpecialPrem").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
ActiveSheet.Paste Destination:=Worksheets(“DataSpecialPrem”).Rows(cutrow)
End If

Next i

End Sub]

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[shrivallabha]

Welcome to Chandoo.org forums.

From the code it seems that you are trying to match entire cell contents.

So basically, you want to cut and paste all rows that are containing either "Premium" or "Special" in column D. Is it correct?

 

[CLoos]

Below is the code to loop through and move the row. My struggle with this is too be able to find the word "Premium" within a cell never at the same position within a cell. I want to also look for the word "Special" within a cell. I assume I need to do an OR within the IF statement. Do I need to create a variable and use that? It is the equal signs that are confusing me. Thanks for any help.

[Sub Move_Data()
Dim i As Long, LastRow As Long, mydata As String, cutrow As Long

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row

Application.ScreenUpdating = True

For i = LastRow To 2 Step -1
mydata = Cells(i, “D”)

If mydata = Range("D3:D5000").Find("Premium", Range("D3"), xlValues, xlWhole, xlByColumns, xlNext).row Then
Cells(i, “D”).EntireRow.Cut
cutrow = Worksheets("DataSpecialPrem").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
ActiveSheet.Paste Destination:=Worksheets(“DataSpecialPrem”).Rows(cutrow)
End If

Next i

End Sub]

__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

Welcome to Chandoo.org forums.

From the code it seems that you are trying to match entire cell contents.

So basically, you want to cut and paste all rows that are containing either "Premium" or "Special" in column D. Is it correct?

 

[CLoos]

Yes, you are correct. When trying to find solutions using "find" seemed to be the answer. I also created a variable and tried that but nothing I am doing is working. So what is it that you see that seem to match the entire contents? And how do I fix it?
Thank you!

 

[shrivallabha]

It is one argument in .Find method. When you choose "xlWhole" then it will match entire cell contents (option you have selected). If this is not what you are after then you need to use "xlPart" which will do a partial match. Please read VBA reference help for details.

You can use autofilter without loop like below to get this done like below which is lightly tested code.

Sub CutPasteData()
Dim lngLastRow As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:D" & lngLastRow)
    .AutoFilter 4, "*premium*", xlOr, "*special*"
    On Error Resume Next
    Range("A2:A" & lngLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("DataSpecialPrem").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range("A2:A" & lngLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With
End Sub

 

[CLoos]

It is one argument in .Find method. When you choose "xlWhole" then it will match entire cell contents (option you have selected). If this is not what you are after then you need to use "xlPart" which will do a partial match. Please read VBA reference help for details.

You can use autofilter without loop like below to get this done like below which is lightly tested code.

Sub CutPasteData()
Dim lngLastRow As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:D" & lngLastRow)
    .AutoFilter 4, "*premium*", xlOr, "*special*"
    On Error Resume Next
    Range("A2:A" & lngLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("DataSpecialPrem").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range("A2:A" & lngLastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
End With
End Sub

Awesome Thank you! I even knew the xlWhole vs. xlPartial but I had become so frustrated with this I missed it. You saved the day!