VBA find non-matching value in a column

[Ron903]

Hello, looking for some assistance with a macro. I am trying to figure out how to search a column to locate the 1st cell that does NOT match a specific value. I would like to avoid looping thru rows as the spreadsheet can be rather large (100's of columns x 1000's of rows).

For example:
Column A contains only 2 possible values: "P" and something else, say "String A" but this will vary. So need to search/find <> "P" to extract "String A" for copying to another sheet.
Column B contains only 2 possible values: "P" and something else, say "String B" but this will vary. So need to search/find <> "P" to extract "String B" for copying to another sheet.
...and so on.

Thanks for any help and suggestions.
Ron

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

For example, if you are using Excel 365, if I understand you correctly, I think you can use the new FILTER function to do this quite easily.
See: FILTER function - Microsoft Support

 

[Ron903]

Thanks Joe for the tip on the Account Details.

...and on FILTER. I see how for example =FILTER(GS2:GT11,GS2:GS11<>"P") works in my spreadsheet. Just not sure how to incorporate this function into VBA to extract the 1st occurrence of a non P value, when the columns that I am needing to search aren't in the exact same position for every project. They are identified by column headings/numbers derived from a loop.

For additional clarification:

COL_A	COL_B	COL_C
P	NEED THIS VALUE	P
P	P	P
NEED THIS VALUE	P	P
P	P	NEED THIS VALUE
P	P	P

 

[Joe4]

So, how do you know what columns to search? What is the logic?

I am having a hard-time visualizing everything from your explanations.
Can you show us some real examples of what your data looks like AND your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in. One of the nice things about this tool is that it will show us the row and column headers so that we can see where everything resides.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Akuini]

I would like to avoid looping thru rows as the spreadsheet can be rather large (100's of columns x 1000's of rows).

Actually it is significantly faster if you do it in memory (via an array).
Here's an example to search in col A:

Sub Ron903()
Dim i As Long
Dim va

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))

For i = 2 To UBound(va, 1)  'i = 2 if data start at row 2

    If va(i, 1) <> "P" Then
        Debug.Print "A" & i  'the first cell that meets the criteria
        Exit For
    End If
  
Next

End Sub

 

[Ron903]

Actually it is significantly faster if you do it in memory (via an array).
Here's an example to search in col A:

Sub Ron903()
Dim i As Long
Dim va

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))

For i = 2 To UBound(va, 1)  'i = 2 if data start at row 2

    If va(i, 1) <> "P" Then
        Debug.Print "A" & i  'the first cell that meets the criteria
        Exit For
    End If
 
Next

End Sub

Thank you Akuini, I was able to use a slightly modified version of this example, along with a few tweaks to my original code to minimize the # of columns that need searching, and it ran pretty quick.

And thanks also Joe4 for your suggestions (and patience).

I need to stop over complicating things. Sigh.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.