VBA IF, Then using Left, Right, Mid functions

[wilkisa]

Every month I have to identify the entries in a company bank statement, generally not more than 2000 lines long. The first entry always begins on row 19. I need the VBA to analyze the data in Cell A19 and either put the identity in Cell I19 or leave I19 blank. There are dozens of possible identities within multiple groups. My VBA skills were never strong and are now rusty, but if someone can help me get the first group, I can manage the rest.

First group example:
If RIGHT(A19,15) = USBLXXXXX7200SB, Then I19 should be #12345 OR
If RIGHT(A19,15) = USBLXXXXX3003SB, Then I19 should be #23456 OR
If RIGHT(A19,15) = USBLXXXXX4450SB, Then I19 should be #34567

Once it has analyzed each item in the group, it should proceed to the next group. A CALL, I believe. The next group might be something like:
If LEFT(A19,6) = COUGAR, then I19 should be "Acct 1234" OR
If LEFT(A19,6) = BOBCAT, then I19 should be "Acct 2345" OR
If LEFT(A19,6) = OCELOT, then I19 should be "Acct 3456"
Next group

After each group has analyzed A19 and either put the result in I19 or left it as is, it should move to A20 and do it all over again to the bottom of the statement, not more than 2000 lines. Also, if I19 already has data, skip to next cell as there will never be two possible results for I19.

Can someone set up the first group for me? I can take it from there, I think.

Thank you in advance for any help I can get with this.

 

[Oaktree]

Sounds like a combination of Select Case True and "Like" (with ? wildcards where you have X) will be what you're looking for:

Dim i
For i = 19 To Cells(Rows.Count, 1).End(xlUp).Row

    If Len(Cells(i, "I")) = 0 Then

        Select Case True
            Case UCase(Right(Cells(i, 1), 15)) Like "USBL?????7300SB"
                Cells(i, "I") = "#12345"
            Case UCase(Right(Cells(i, 1), 15)) Like "USBL?????7300SB"
                Cells(i, "I") = "#23456"
            '...
            Case UCase(Left(Cells(i, 1), 6)) = "COUGAR"
                Cells(i, "I") = "Acct 1234"
            '...
        End Select
    
    End If
    
Next i

 

[wilkisa]

Thank you, Oaktree! I'll give it a try. I appreciate it!

 

[wilkisa]

I'm not getting anywhere with the CASE statement. I dummied some data to give you an idea what I need and how the source data looks. In the image, the data in BOLD GREEN text is what should be analyzed in column A. The result should go to column I. Some entries will not need a result in column I. Some cells will trigger on data at the right end of the string, some will trigger on the left of the string. I can't use wildcards because the items in BOLD GREEN are exactly what is on the statement and due to the sheer volume of actual data, I have to present it as is. The items in BOLD RED is what the results should be. I hope this makes sense.