Hi.
I would like to change this structure IF to CASE.
can anyone help me?
Thank you!
Thank's![]()
Sub TestCase() Dim LR As Long Rows(6).Resize(100).Delete If Range("a2") = "" And Range("b2") <> "" And Range("c2") <> "" Then With Sheets("test").UsedRange .AutoFilter Field:=3, Criteria1:=Range("b2") .AutoFilter Field:=11, Criteria1:=Range("c2") .Copy Range("a6") .AutoFilter End With ElseIf Range("a2") <> "" And Range("b2") = "" And Range("c2") <> "" Then With Sheets("test").UsedRange .AutoFilter Field:=1, Criteria1:=Range("a2") .AutoFilter Field:=11, Criteria1:=Range("c2") .Copy Range("a6") .AutoFilter End With ElseIf Range("a2") <> "" And Range("b2") <> "" And Range("c2") = "" Then With Sheets("test").UsedRange .AutoFilter Field:=1, Criteria1:=Range("a2") .AutoFilter Field:=3, Criteria1:=Range("b2") .Copy Range("a6") .AutoFilter End With ElseIf Range("a2") = "" And Range("b2") = "" And Range("c2") <> "" Then With Sheets("test").UsedRange .AutoFilter Field:=11, Criteria1:=Range("c2") .Copy Range("a6") .AutoFilter End With ElseIf Range("a2") <> "" And Range("b2") = "" And Range("c2") = "" Then With Sheets("test").UsedRange .AutoFilter Field:=1, Criteria1:=Range("a2") .Copy Range("a6") .AutoFilter End With ElseIf Range("a2") = "" And Range("b2") <> "" And Range("c2") = "" Then With Sheets("test").UsedRange .AutoFilter Field:=3, Criteria1:=Range("b2") .Copy Range("a6") .AutoFilter End With Else With Sheets("test").UsedRange .AutoFilter Field:=1, Criteria1:=Range("a2") .AutoFilter Field:=3, Criteria1:=Range("b2") .AutoFilter Field:=11, Criteria1:=Range("c2") .Copy Range("a6") .AutoFilter End With End If End Sub
Cross-Post
http://www.excelkey.com/forum/viewto...7&p=3566#p3566
Bookmarks