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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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.
 

Forum statistics

Threads
1,089,674
Messages
5,409,655
Members
403,274
Latest member
Lewis1995

This Week's Hot Topics

Top