Select Case Statements

[UberFrank]

Hi guys, can anyone explain why the following case statement isnt working? Intead of replacing ALL with * it just skips straight to the else statement and uses all.

Sub Macro1()
    Dim startdate As Long, enddate As Long, Client As String, Depot As String
 
    startdate = CLng(DateValue(Sheets("Reports & Archive").Range("B8")))
    enddate = CLng(DateValue(Sheets("Reports & Archive").Range("D8")))
 
    Select Case Sheets("Reports & Archive").Range("H8").Value
    Case "ALL"
    Client = "*"
    Case Else
    Client = Sheets("Reports & Archive").Range("H8").Value
    End Select
    Select Case Sheets("Reports & Archive").Range("F8").Value
    Case "ALL"
    Depot = "*"
    Case Else
    Depot = Sheets("Reports & Archive").Range("F8").Value
    End Select
 
 
    Sheets("Archived Escalations").Activate
 
    Sheets("Archived Escalations").Copy
    ChDir "[URL="file://\\888888888888888888888"]\\888888888888888888888[/URL]"
    ActiveWorkbook.SaveAs Filename:= _
        "[URL="file://\\88888888888888888888"]\\88888888888888888888[/URL]report.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveSheet.Unprotect
    Range("B2:J2").Select
 
    Selection.AutoFilter Field:=1, Criteria1:=">=" & startdate, Operator:=xlAnd _
        , Criteria2:="<=" & enddate
 
    Selection.AutoFilter Field:=5, Criteria1:="" & Client
    Selection.AutoFilter Field:=6, Criteria1:="" & Depot
 
End Sub

Thanks in advance

 

[Andrew Poulsom]

How do you know that? Are you sure that the cell contains ALL?

 

[UberFrank]

Hi Andrew,

Yes the cell does contain the text ALL. I have tested this and all works except that the custom filter serches for "All" instead of "*"

Thanks

 

[Andrew Poulsom]

ALL isn't the same as All. Try:

Select Case UCase(Sheets("Reports & Archive").Range("H8").Value)

 

[UberFrank]

Thank you very much Ser, this has resolved the issue.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Much appreciated<o></o>