asked on

Macro Excel - if condition - Start by AND contain

Hello Expert,

I need your help in order to have an excel macro that will move the entire row to sheet "TableVDI" that will meet criterias:

From sheet "TableVM"
for each row of the table
if (cell A start by VM-C% and also contain "%ER%") or
(cell A start by VM-C% and also contain "%FR%") or
(cell A start by VMC% and also contain "%FD%") or
(cell A start by VMC% and also contain "%ED%") or
(cell A start by VMC% and also contain "%XD%")
Move the entire row to sheet "TableVDI"
end if
Next

I did attach a sample of file to that question. Hope it will help.

Thanks,

Shums Faruk

Hi,

Which is your first row/column and last row/column?

mldaigle1

ASKER

Did I forgot to attach the file?

Duh....

from sheet TableVM A2 to the end of the table and move the appropriate rows sheet TableVDI starting at A2 and append rows after
test.xlsx

Shums Faruk

Hi,

Please run below macro (CopyFilteredRow) and let me know:

Sub CopyFilteredRow()
Dim SrcWs As Worksheet, NewSh As Worksheet
Dim SrcLR As Long, FshLR As Long
Dim j As Long, i As Long
Dim Crit(6) As String
Dim StrIF As String
Dim RngFilter As Range
Dim xWs As Worksheet
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
'Delete Sheets if it exists
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
    If xWs.Name = "TableVDI" Then
        xWs.Delete
    End If
Next
Application.DisplayAlerts = True
Set SrcWs = Worksheets("TableVM")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row
Crit(0) = "*ER*"
Crit(1) = "*FR*"
Crit(2) = "*FD*"
Crit(3) = "*ED*"
Crit(4) = "*XD*"
Crit(5) = "VM-C*"
Crit(6) = "VMC*"

With SrcWs
    .AutoFilterMode = False
    .Range("S1").Value = "Temp"
    For j = 2 To SrcLR
        StrIF = "=if(or(isnumber(search(" & Chr(34) & Crit(0) & Chr(34) & ",A" & j & ")),isnumber(search(" & Chr(34) & Crit(1) & Chr(34) _
            & ",A" & j & ")),isnumber(search(" & Chr(34) & Crit(2) & Chr(34) & ",A" & j & ")),isnumber(search(" & Chr(34) & Crit(3) & Chr(34) & ",A" & j & "))),999,0)"
        .Range("S" & j).Formula = StrIF
    Next j
End With

Set RngFilter = SrcWs.Range("A1:S" & SrcLR)
With RngFilter
    .AutoFilter Field:=19, Criteria1:="999", Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).Copy
    Worksheets.Add.Paste
End With
SrcWs.Columns(19).Delete
ActiveSheet.Name = "TableVDI"
Set NewSh = Worksheets("TableVDI")
Application.CutCopyMode = False
If SrcWs.AutoFilterMode = True Then SrcWs.AutoFilterMode = False
NewSh.Activate
NewSh.Columns(19).Delete
NewSh.Columns.AutoFit
NewSh.Range("B2").Select
ActiveWindow.FreezePanes = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

mldaigle1

ASKER

Hello,

Unfortunately, the search criteria is not working. So i did a test an modify it as follow:

Crit(0) = "*VM-C*ER*"
Crit(1) = "*VM-C*FR*"
Crit(2) = "*VMC*FD*"
Crit(3) = "*VMC*ED*"
Crit(4) = "*VMC*XD*"

It kinda worked except for the last criteria. (the XD did not copy in the TableVDI). Also, the complete table is from A1:T, i forgot 2 columns in the sample file.

Macro do a copy into TableVDI and does not delete the row in TableVM.

Shums Faruk

Check attached...and highlight those which were not working, I will make changes.

Macro do a copy into TableVDI and does not delete the row in TableVM.

Initially you didn't ask for it
Copy-Filtered-Rows_test_11.xlsm

mldaigle1

ASKER

oops, sorry, I assume that my initial request of moving a row was meaning copy and delete. My mistake.

I did modify the file as requested. All rows that should move into TableVDI have been identify in column J
Copy-Filtered-Rows_test_11.xlsm

Shums Faruk

You mean the 1 in Column J, should be moved?

Shums Faruk

Please find attached. Copying and deleting filtered row.

From attached. Please highlight which should still be copied from TableVM or which are copied wrongly in Table VDI
Copy-Filtered-Rows_test_12.xlsm

mldaigle1

ASKER

Only the one higlighted should be copy to TableVDI and delete from TableVM

Please note that i have info in column S and T as well,
Copy-Filtered-Rows_test_12a.xlsm

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mldaigle1

ASKER

Yes!!

This is working great! Thanks again Shums
Have a nice weekend!

Shums Faruk

You're Welcome! Glad I was able to help
You too have a nice weekend