VBA Macro: If Begins With Then

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
I am failing dramatically at writing the code for this, what I thought would be a simple macro.

This initial process is the following:

IF E2 begins with “006***” THEN return the last 8 characters in the string in U2, only IF the final 8 characters are numbers, THEN return “N/A” in U2, & IF there are no characters after “006***” THEN return “RESEARCH” in U2.

Else

IF E2 begins with “1Z” THEN return “UPS” in U2

Else

IF E2 begins with “UPS” THEN return “UPS” in U2

Else

IF E2 contains “1Z” in string THEN return “UPS” in U2

Else

IF E2 contains “” (blank) in string THEN return “Research” in U2

This macro needs to be setup to work on the same report I pull everyday (its static and always the same), and needs to encompass all entries in the "E" Column.

The Data in column E looks similar to this:

[TABLE="width: 275"]
<colgroup><col></colgroup><tbody>[TR]
[TD]006ATL95210452[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL97146350[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]006MSP95230612[/TD]
[/TR]
[TR]
[TD]006ATL95234230[/TD]
[/TR]
[TR]
[TD]1Z22A9000273228290[/TD]
[/TR]
[TR]
[TD]006ATL95241941[/TD]
[/TR]
[TR]
[TD]UPS1Z22A9000276792808[/TD]
[/TR]
[TR]
[TD]UPS 1Z58F0260240282635[/TD]
[/TR]
[TR]
[TD]006ATL95248672[/TD]
[/TR]
[TR]
[TD]FEDEX#645003799830[/TD]
[/TR]
[TR]
[TD]006ATL95252010[/TD]
[/TR]
[TR]
[TD]006ATL95401692

Any help from you MACRO kings would be appreciated!

Steve[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi
How about
Code:
Sub CheckColE()

    Dim Cl As Range

    For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
        Select Case True
            Case Left(Cl.Value, 3) = "006"
                If IsNumeric(Right(Cl.Value, 8)) Then
                    Cl.Offset(, 16).Value = Right(Cl.Value, 8)
                End If
            Case Cl.Value Like "1Z*" Or Cl.Value Like "UPS*"
                Cl.Offset(, 16).Value = "UPS"
            Case Len(Cl.Value) = 0
                Cl.Offset(, 16).Value = "Research"

        End Select
    Next Cl
            

End Sub
 
Upvote 0
Hi
How about
Code:
Sub CheckColE()

    Dim Cl As Range

    For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
        Select Case True
            Case Left(Cl.Value, 3) = "006"
                If IsNumeric(Right(Cl.Value, 8)) Then
                    Cl.Offset(, 16).Value = Right(Cl.Value, 8)
                End If
            Case Cl.Value Like "1Z*" Or Cl.Value Like "UPS*"
                Cl.Offset(, 16).Value = "UPS"
            Case Len(Cl.Value) = 0
                Cl.Offset(, 16).Value = "Research"

        End Select
    Next Cl
            

End Sub

Brilliant! I am so incredibly frustrated that I am having a hard time figuring out this VBA shhhhi...Stuff. There seems to be a hundred different ways to write a sub process that will all do the exact same thing. Thanks, I do very much appreciate your skill set.
 
Upvote 0
@Fluff,

I added in a bunch of other coded lines using your code from above and ran into a problem.

Case mycell.Value Like "CHART*"
mycell.Offset(, 16).Value = "CHARTER"

It brought everything back correctly per your code. However, it wouldn't return "CHARTER" for some of the cells that actually say "charter".

It is lowercase, so I attempted the code:

Case mycell.Value Like "CHART*" Or mycell.Value Like "charter"
mycell.Offset(, 16).Value = "CHARTER"

But it still didn't return "CHARTER", it just returns a blank. Do I need to account for values that are in lowercase and uppercase?

It also didn't account for the UPS "1Z" values that are "1z" in the cell.

Thanks, Steve
 
Last edited:
Upvote 0
Glad to help & thanks for the feedback

There seems to be a hundred different ways to write a sub process that will all do the exact same thing
Agreed, sometimes I find it a blessing & other times a pain in the whatsits!
 
Upvote 0
It's case sensitive, but one way round that is
Code:
            Case [COLOR=#0000ff]UCase[/COLOR](myCell.Value) Like "CHART*"
                myCell.Offset(, 16).Value = "Charter"
Where the code in blue converts the value to Upper case, alternatively you could do the opposite
Code:
            Case LCase(myCell.Value) Like "chart*"
                myCell.Offset(, 16).Value = "Charter"
 
Upvote 0
@Fluff

Great! Works great. I've been able to create the entire macro based on your coding. Thank you!
 
Upvote 0
@Fluff

You'll notice the last 3 entries in the data set begin with "006", however, the alphabetic characters are at the end of the string. This poses a new question, I can account for the what the string begins with and what the string ends with, but I'm stuck on how to write the code to pull the information in-between. Below is your code mixed in with my "non-working" code as I have attempted to create a rule that accounts for these random outliers.

Select Case True
Case Left(mycell.Value, 3) = "006"
If IsNumeric(Right(mycell.Value, 8)) Then
mycell.Offset(, 16).Value = Right(mycell.Value, 8)
End If
Case Left(mycell.Value, 2) = "06"
If IsNumeric(Right(mycell.Value, 8)) Then
mycell.Offset(, 16).Value = Right(mycell.Value, 8)
Case Left(mycell.Value, 3) = "006"
If Not IsNumeric(Right(mycell.Value, 8)) Then
mycell.Value = Mid(mycell.Value, 3, 8)
End If
End Select

Ideas?

[TABLE="width: 114"]
<colgroup><col></colgroup><tbody>[TR]
[TD]006ATL95210452[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD]006MSP95212176[/TD]
[/TR]
[TR]
[TD]006DTW95214475[/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL95219924[/TD]
[/TR]
[TR]
[TD]006ATL97146350[/TD]
[/TR]
[TR]
[TD]006MSP95228184[/TD]
[/TR]
[TR]
[TD]006MSP95230612[/TD]
[/TR]
[TR]
[TD]00695446750MSP[/TD]
[/TR]
[TR]
[TD]00695466766MSP[/TD]
[/TR]
[TR]
[TD]00695466044MSP


[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
A Select Case statement works in the same way as an If statement. So if
Code:
            Case Left(Cl.Value, 3) = "006"
is true, it will do whatever code comes before the following Case & then jumps to End Select. So to do 2 or more things you have to do it like this
Code:
            Case Left(Cl.Value, 3) = "006"
                If IsNumeric(Right(Cl.Value, 8)) Then
                    Cl.Offset(, 16).Value = Right(Cl.Value, 8)
                Else
                    Cl.Offset(, 16).Value = Mid(Cl.Value, 4, 8)
                End If
            Case Cl.Value Like "1Z*" Or Cl.Value Like "UPS*"
Hope that makes sense
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

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