Filter column on text value if that value exists using VBA

[Ibam44]

Hi all,

I am new to using VBA and new here as well with my first post, but hoping someone can help solve an issue I'm running into. Please forgive any omission errors and explanation shortfall.
In a worksheet, I am pulling data in from an outside source into columns I:M (with varying lengths(rows) of data), then using xlookup formulas to populate values into N:O all with headers on Row 1. I am initially filtering column J to eliminate any zero values, then want to filter column O only if the value of "*UpdateMasterList" value exists in that column. If it does exist, then the filter should be applied and I want to copy all of the filtered data from A1:O and paste values into a new sheet. If that value doesn't exist, the code should jump to the next step.

Here is my code for these steps. However, it just jumps over the filtering for "*UpdateMasterList" and copies everything to the new sheet.

 Sheets("Test").Select
    Columns("I:O").Select
    Selection.AutoFilter
    Range("O2").Select
    ActiveSheet.Range("$I$1:$O$570").AutoFilter Field:=2, Criteria1:="<>0", _
        Operator:=xlAnd
    If Range("O2" & LastRow).Value = "*UpdateMasterList" Then
        ActiveSheet.Range("$I$1:$O$570").AutoFilter Field:=7, Criteria1:="*UpdateMasterList"
    Cells.Select
    Range("I2:O" & LastRow).Copy
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Else
    End If

 

[Micron]

Basic troubleshooting 101 is step through the code (F8) when it doesn't do what you expect. Watch how it executes - it may be skipping over lines because your IF block result is not what you expect. You can mouse over variables or test them in the immediate window by typing

?Range("O2" & LastRow) = "*UpdateMasterList"

and hit Enter at the end of that line. The result will be either a True or False is returned. Note that for a code line that tests a variable the variable must have already been assigned a value, or the testing line must execute first. Example:

myString = "this is a test"
If you stop at this line, myString will not equal "this is a test" since the line has not executed yet (unless you're repeating code, which would not make sense).

 

[Ibam44]

Thanks for this Micron. I am an old dog trying to learn new tricks, so this process is a bit slow for me.

I have been stepping through using F8 and watching it skip over just unsure as to why.

When I type ?Range("O2" & LastRow) = "*UpdateMasterList" and hit enter it changes to Print Range("O2" & LastRow) = "*UpdateMasterList" which obviously isn't what I'm looking for.

Do I need to set a variable for "*UpdateMasterList", or can I just search for that string of characters? What should the IF statement look like to see if the column contains that specified text?

 

[Micron]

I'd guess you're not doing that in the immediate window as suggested. It will not alter your typing but the editor will.

If that doesn't help and you want to upload your file to a drop box I can take a look at it.

 

[Ibam44]

I didn't know that window existed, thanks! I can work through this from here.

 

[Peter_SSs]

In case it has not become clear, one of your problems is definitely the line

If Range("O2" & LastRow).Value = "*UpdateMasterList" Then

Suppost LastRow is100 then this line becomes

If Range("O2" & 100).Value = "*UpdateMasterList" Then

.. and then

If Range("O2100).Value = "*UpdateMasterList" Then

So instead of looking at cell O100 the code is actually looking at cell O2100 which presumably is well below all your data & therefore will never equal "*UpdateMasterList" hence the following AutoFilter line will never be executed.

You probably want

If Range("O" & LastRow).Value = "*UpdateMasterList" Then

BTW, is that asterisk in "*UpdateMasterList" an actual asterisk or are you trying to use that as a wildcard?

 

[Ibam44]

Peter, thank your for that explanation, that is going to help me a lot as I try and do more of this going forward.

Ultimately, a colleague suggested I remove the IF statement entirely for now and handle it separately on a different sheet.

I appreciate all of the feedback here as I venture into this world.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.