Trap when Filter returns nothing

Bah73 · Mar 24th 2023

Hi

Is anyone able to point me in the right direction on where to get help with some MS Project VBA code?

**Carim** · Mar 24th 2023

Hi,

For sure the Forum can help you out

Could you summarize your general situation ... and explain the main obstacle you are facing ...

Bah73 · Mar 24th 2023

Thanks Carim,

Basically I am applying a column filter then adding a value to an adjacent column

I problem I am having is when the filter returns no value the code still adds a value to the adjacent column

This creates a new task line

The code I need help with is if the filter returns no values to skip to next filter block

I cannot upload a Project file so have added some code below

Code

'''''Examples of a filter returning no value and adding format and text to adjacent column creating a new task
'''''When filter returns no value I need to skip to next set of code

'''''Skip if no value returned 
    SetAutoFilter FieldName:="Text30", FilterType:=pjAutoFilterCustom, Test1:="contains", Criteria1:="ALS"
    SetAutoFilter FieldName:="Resource Names", FilterType:=pjAutoFilterIn, Criteria1:="PC-CPINSP"
    SelectTaskColumn Column:="Text3"
    Font32Ex CellColor:=13159680
    SetTaskField Field:="Text3", Value:="CLASSIFIED"
    FillDown
    FilterClear
     

'''''Skip if no value returned   
    SetAutoFilter FieldName:="Text30", FilterType:=pjAutoFilterCustom, Test1:="contains", Criteria1:="ALS"
    SetAutoFilter FieldName:="Resource Names", FilterType:=pjAutoFilterIn, Criteria1:="PC-CRNINSP"
    SelectTaskColumn Column:="Text3"
    Font32Ex CellColor:=13159680
    SetTaskField Field:="Text3", Value:="Crane"
    FillDown
    FilterClear
    
    
'''''Skip if no value returned     
    SetAutoFilter FieldName:="Text30", FilterType:=pjAutoFilterCustom, Test1:="contains", Criteria1:="ALS"
    SetAutoFilter FieldName:="Resource Names", FilterType:=pjAutoFilterIn, Criteria1:="PC-DRONE"
    SelectTaskColumn Column:="Text3"
    Font32Ex CellColor:=13159680
    SetTaskField Field:="Text3", Value:="Drone"
    FillDown
    FilterClear

Display More

**Carim** · Mar 24th 2023

Hi again,

Given the fact you do need to trap when Filter returns nothing, the best solution is to have your UserDefinedFunction

see :

How To Trap when Autofilter returns nothing

I couldn't really figure out how to title this, but here's a bit more in depth description. I have created a VBA code which will filter an excel file based on…

stackoverflow.com

Bah73 · Mar 24th 2023

Thanks Carim

**Carim** · Mar 24th 2023

You are welcome

Thanks for your Thanks AND for the Like

Trap when Filter returns nothing

Carim Mar 24th 2023

Participate now!

Share

Users Viewing This Thread