If loop skipping criteria

dshafique

Board Regular
Joined
Jun 19, 2017
Messages
150
Hi guys, I'm trying to write code which checks if the column "Status" in "yTable" has the string "yes" in it. if it does, i want it to do a series of things, if not, then I want it to go to a different sheet and do that. This is what I have so far.

Code:
 If "yTable[[Status]:[Status]]" = "yes" Then          ActiveSheet.ListObjects("yTable").Range.AutoFilter Field:=13, Criteria1:= _
        "yes"
        Columns("M:M").Select
    Selection.EntireColumn.Hidden = True
   ActiveWorkbook.Worksheets("Yesterday").ListObjects("yTable").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Yesterday").ListObjects("yTable").Sort.SortFields. _
        Add Key:=Range("yTable[[#All],[Name]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Yesterday").ListObjects("yTable").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
        Sheets("Data").Select
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveSheet.ListObjects("dtable").ListRows.Add AlwaysInsert:=True
        Sheets("Yesterday").Select
        Range("A1").Select
        ActiveSheet.ListObjects("yTable").DataBodyRange.Select


        Selection.Copy
        Sheets("Data").Select
        Range("A1").Select
        Selection.End(xlDown).Select
        
       ActiveCell.Offset(1).Select
        ActiveSheet.Paste
        
 
        
        
        Range("A1").Select
        
        
      
 
 ActiveWorkbook.Worksheets("Data").ListObjects("dTable").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").ListObjects("dTable").Sort.SortFields.Add _
        Key:=Range("dTable[[#All],[Name]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").ListObjects("dTable").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
 
Else: Sheets("Data").Select
End If
it always breaks out of the loop and goes to the other sheet, even when it says yes clearly in the column.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe...

Code:
If Application.CountIf(ActiveSheet.ListObjects("yTable").ListColumns("Status").DataBodyRange, "yes") Then
    'do something
Else
    'do other thing
End If
Hope this helps

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,995
Messages
5,411,724
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top