If loop skipping criteria

[dshafique]

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.

 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.

 

[Marcelo Branco]

Maybe...

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

Hope this helps

M.

 

[dshafique]

thanks, that's just what I needed.