We help IT Professionals succeed at work.

Macro Excel - if condition - Start by AND contain

196 Views
Last Modified: 2017-04-07
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,
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi,

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

Author

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You mean the 1 in Column J, should be moved?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes!!

This is working great!  Thanks again Shums
Have a nice weekend!
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.