| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Testing of 3 cells
I have a large spreadsheet that runs a macro using the advance filter to
isolate data and change the carts respectivly. The criteria range is b2:d2, the macro and filter works well as long as there is information in cell b2. If I only use cells c2 and d2 to sort the information the code breaks. What I am trying to accomplish is if cells b2:d2 are all blank then showalldata, if there is one of these cells that has information in it sort by that information. Here is some of the codes that I have tryed: If (IsEmpty("b2") And IsEmpty("C2") And IsEmpty("D2")) Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If If Range ("b2:d2") = "" Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If Thank you for your help. Tim Peter |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Testing of 3 cells
you still need to use RANGE
If (IsEmpty("b2") And IsEmpty("C2") And should be If IsEmpty(Range("b2")) And IsEmpty(Range("C2")) And .... elseif .... and so on "tpeter" wrote: I have a large spreadsheet that runs a macro using the advance filter to isolate data and change the carts respectivly. The criteria range is b2:d2, the macro and filter works well as long as there is information in cell b2. If I only use cells c2 and d2 to sort the information the code breaks. What I am trying to accomplish is if cells b2:d2 are all blank then showalldata, if there is one of these cells that has information in it sort by that information. Here is some of the codes that I have tryed: If (IsEmpty("b2") And IsEmpty("C2") And IsEmpty("D2")) Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If If Range ("b2:d2") = "" Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If Thank you for your help. Tim Peter |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Testing of 3 cells
Patrick,
Thank you for your response. The code is only recognizing B2. If this cell is populated then all 3 criteria will filter. If I only use cells c2 and d2 (leaving b2 blank) the if statement wants to unfilter the spreadsheet and ignores the criteria in cells c2 and d2. The information in these cells is based on a drop down list could this have anything to do with my issue's? If IsEmpty(Range("b2")) And IsEmpty(Range("c2")) And IsEmpty(Range("d2")) Then ActiveSheet.ShowAllData Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If "Patrick Molloy" wrote: you still need to use RANGE If (IsEmpty("b2") And IsEmpty("C2") And should be If IsEmpty(Range("b2")) And IsEmpty(Range("C2")) And .... elseif .... and so on "tpeter" wrote: I have a large spreadsheet that runs a macro using the advance filter to isolate data and change the carts respectivly. The criteria range is b2:d2, the macro and filter works well as long as there is information in cell b2. If I only use cells c2 and d2 to sort the information the code breaks. What I am trying to accomplish is if cells b2:d2 are all blank then showalldata, if there is one of these cells that has information in it sort by that information. Here is some of the codes that I have tryed: If (IsEmpty("b2") And IsEmpty("C2") And IsEmpty("D2")) Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If If Range ("b2:d2") = "" Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If Thank you for your help. Tim Peter |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Testing of 3 cells
Patrick,
My Bad I had a typo, your suggestion worked perfectly. Thanks again for your help. "tpeter" wrote: Patrick, Thank you for your response. The code is only recognizing B2. If this cell is populated then all 3 criteria will filter. If I only use cells c2 and d2 (leaving b2 blank) the if statement wants to unfilter the spreadsheet and ignores the criteria in cells c2 and d2. The information in these cells is based on a drop down list could this have anything to do with my issue's? If IsEmpty(Range("b2")) And IsEmpty(Range("c2")) And IsEmpty(Range("d2")) Then ActiveSheet.ShowAllData Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If "Patrick Molloy" wrote: you still need to use RANGE If (IsEmpty("b2") And IsEmpty("C2") And should be If IsEmpty(Range("b2")) And IsEmpty(Range("C2")) And .... elseif .... and so on "tpeter" wrote: I have a large spreadsheet that runs a macro using the advance filter to isolate data and change the carts respectivly. The criteria range is b2:d2, the macro and filter works well as long as there is information in cell b2. If I only use cells c2 and d2 to sort the information the code breaks. What I am trying to accomplish is if cells b2:d2 are all blank then showalldata, if there is one of these cells that has information in it sort by that information. Here is some of the codes that I have tryed: If (IsEmpty("b2") And IsEmpty("C2") And IsEmpty("D2")) Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If If Range ("b2:d2") = "" Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If Thank you for your help. Tim Peter |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Testing of 3 cells
no worries. cheers
"tpeter" wrote: Patrick, My Bad I had a typo, your suggestion worked perfectly. Thanks again for your help. "tpeter" wrote: Patrick, Thank you for your response. The code is only recognizing B2. If this cell is populated then all 3 criteria will filter. If I only use cells c2 and d2 (leaving b2 blank) the if statement wants to unfilter the spreadsheet and ignores the criteria in cells c2 and d2. The information in these cells is based on a drop down list could this have anything to do with my issue's? If IsEmpty(Range("b2")) And IsEmpty(Range("c2")) And IsEmpty(Range("d2")) Then ActiveSheet.ShowAllData Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If "Patrick Molloy" wrote: you still need to use RANGE If (IsEmpty("b2") And IsEmpty("C2") And should be If IsEmpty(Range("b2")) And IsEmpty(Range("C2")) And .... elseif .... and so on "tpeter" wrote: I have a large spreadsheet that runs a macro using the advance filter to isolate data and change the carts respectivly. The criteria range is b2:d2, the macro and filter works well as long as there is information in cell b2. If I only use cells c2 and d2 to sort the information the code breaks. What I am trying to accomplish is if cells b2:d2 are all blank then showalldata, if there is one of these cells that has information in it sort by that information. Here is some of the codes that I have tryed: If (IsEmpty("b2") And IsEmpty("C2") And IsEmpty("D2")) Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If If Range ("b2:d2") = "" Then Call ShowAll Else Range("A13:N18754").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B1:D2"), Unique:=False End If Thank you for your help. Tim Peter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Testing of an array | Excel Worksheet Functions | |||
| Conditional testing | Excel Programming | |||
| Help needed for Sumproduct or Other Conditional testing | Excel Discussion (Misc queries) | |||
| Testing Blank Conditional Formatting | Excel Programming | |||
| Conditional Testing Glitch | Excel Programming | |||
Linear Mode
