lundi 8 avril 2019

Conditional IF doesn't match

I have a question regarding the IF conditional and I am wondering if you can help me to resolve it.

I have two Sheets; • Sheet 1 – one table (TbAssign) • Sheet 2 (Inventory) – one table (InventoryDetailTbl) • I have created a macro to copy and paste some columns from Sheet 2 to Sheet1. When the condition is “Triage” on Column #33. The macro is working well. No issues with this (Move the rows with “Triage” to Sheet1). • The issue I have is when the Condition doesn’t match (Column #33 does not contain the word “Triage Triage”, therefore, the macro copies all rows to sheet1, • I have tried to write a code an IF condition but is not working.

Could you please help me understand how I can write this macro when the condition is not matching?

Sub Update_Inventory()

Application.ScreenUpdating = False Dim lrow As Long Dim dstar As Double dstar = Timer

ThisWorkbook.Activate
lrow = Worksheets("Sheet1").Cells(Worksheets("Sheet2").Rows.Count, 4).End(xlUp).Row

lrow = lrow + 1

Worksheets("Inventory").Select

***' I have tried to write a line macro IF condition

    'IF ActiveSheet.Range("InventoryDetailTbl['#33]").Value = "Triage" Then***

      With ActiveSheet.ListObjects("InventoryDetailTbl")
        .Range.AutoFilter Field:=33, _
            Criteria1:="Triage"
        Union(.ListColumns(2).DataBodyRange, _
         .ListColumns(3).DataBodyRange, _
         .ListColumns(4).DataBodyRange, _
         .ListColumns(6).DataBodyRange, _
         .ListColumns(8).DataBodyRange).Copy
     End With

    Worksheets("Sheet1").Select
    Range("D" & lrow).PasteSpecial

    Else

    MsgBox "There isn't new items to move"


    End if

     Worksheets("Inventory").Select

        With ActiveSheet.ListObjects("InventoryDetailTbl")
            .Range.AutoFilter Field:=33
        End With


    Worksheets("Sheet1").Select

Application.ScreenUpdating = True

End Sub

Aucun commentaire:

Enregistrer un commentaire