Find cells containing A two letter string by itself

[TimeDivided]

Hi Folks,

I'm fairly new to VBA, and I'm trying to streamline a few processes at the office. I've perused the boards here (and the internet at large) for a while now and haven't found anything that quite solves what I am trying to do. I'd appreciate any insight at all, even if it's just a point in the right direction of where I should research further

I am sure there are some simple ways to do what I am trying to accomplish if I had the authority to edit the spreadsheet itself, but for reasons not worth getting into, I am unable to modify the layout of the excel sheet, and so am left the task of managing the insanity that I inherited (that never happens right?).

what I have is a travel schedule of sorts, with a non-normalized column in the spreadsheet that contains 3 or 4 different types of data (let's say town names, employee initials, and sometimes a time or other comments). This is part of a larger process I'm working on, but for now, what I want to do is find a way to get VBA to distinguish between the initials, and when the letters in those initials are part of a word. So for example Alex Cuelo's initials would be AC. I want to find all instances of "AC" in the column, but not "NYACK" or "ACTION" etc.

So im thinking something like:

If Activeworksheet.range("B1").value [code to describe "contains AC but only if it is not preceded by or followed by another letter or number"] Then
msgBox("Yes")
Else msgbox("No")
End If

Any help with exactly what code could solve the problem in the bracket would be greatly appreciated.

Thanks!

[JKwan]

give this a try

Sub FindItem()
    Dim Found As Range
    
    Set Found = Find_All(Range("B1"), Range("C1:C1000"), xlValues, xlWhole, True)
    If Not Found Is Nothing Then
        MsgBox "found it - " & Range("B1")
    Else
        MsgBox "Not found - " & Range("B1")
    End If
End Sub
Function Find_All(Find_Item As Variant, Search_Range As Range, _
                  Optional LookIn As XlFindLookIn = xlValues, _
                  Optional LookAt As XlLookAt = xlPart, _
                  Optional MatchCase As Boolean = False) As Range
    Dim c As Range, FirstAddress As String
    Set Find_All = Nothing
    With Search_Range
        Set c = .Find( _
                what:=Find_Item, _
                LookIn:=LookIn, _
                LookAt:=LookAt, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=MatchCase, _
                searchformat:=False)    'Delete this term for XL2000 and earlier
        If Not c Is Nothing Then
            Set Find_All = c
            FirstAddress = c.Address
            Do
                Set Find_All = Union(Find_All, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
End Function

[TimeDivided]

Thank you Dkwan, I have to do a little bit more testing, but I think this will work! For my own learning, can you walk me through your code a bit?

I can see that the sub uses a custom function, and I can loosely understand the arguments for the function, but by the time you've dim'd c, Im lost. I'd love to understand a little bit more about this code so that I understand it's limitations and capabilities.

Thanks again!

[JKwan]

For future reference, it is JKwan (John) not DKwan
I will start with where you left off at dim c, c is defined as a range to use with FIND. If you don't know, if an item is not find, the FIND function with throw an error. That is why a range is defined. If c contains something, then go on to see if there are more occurrences of the item. That is where the FirstAddress comes into play, that Do loop is basically doing that.

If you have more questions, don't be shy, just ask.

[TimeDivided]

Apologies on the name! I had just sent an email to a "Dwarner" and must have still had the "D" on my brain!

This was extremely helpful, thank you!

[JKwan]

No need to apologize, I have fat fingers myself.