VBA Macro: If Begins With Then

[Sphinx404]

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]

 

[Fluff]

Hi
How about

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

 

[Sphinx404]

Hi
How about

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.

 

[Sphinx404]

@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

 

[Fluff]

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!

 

[Fluff]

It's case sensitive, but one way round that is

            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

            Case LCase(myCell.Value) Like "chart*"
                myCell.Offset(, 16).Value = "Charter"

 

[Sphinx404]

@Fluff

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

 

[Fluff]

My pleasure

 

[Sphinx404]

@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]

 

[Fluff]

A Select Case statement works in the same way as an If statement. So if

            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

            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