VBA IF, Then using Left, Right, Mid functions

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sounds like a combination of Select Case True and "Like" (with ? wildcards where you have X) will be what you're looking for:

Code:
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
 
Upvote 0
Thank you, Oaktree! I'll give it a try. I appreciate it!
 
Upvote 0
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.
 

Attachments

  • Test data.jpg
    Test data.jpg
    147.1 KB · Views: 4
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top