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]

 

[Sphinx404]

I'm going to work at it. Let you know if I'm able to figure it out, right now your code above works. I'm going to see my small VBA brain can work its muscles to produce a new rule to exclude other undesirable information. Thanks a lot for your help, when you start teaching classes, let me know.


[TABLE="width: 359"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]695236035[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695235836[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695235302[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]00695446750MSP[/TD]
[TD][/TD]
[TD]95446750[/TD]
[/TR]
[TR]
[TD]00695466766MSP[/TD]
[TD][/TD]
[TD]95466766[/TD]
[/TR]
[TR]
[TD]695493042[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]006DTW[/TD]
[TD][/TD]
[TD]DTW[/TD]
[/TR]
[TR]
[TD]00695466044MSP[/TD]
[TD][/TD]
[TD]95466044[/TD]
[/TR]
[TR]
[TD]ATL00695479042[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695580973[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695513655[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]676183925[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]006AT[/TD]
[TD][/TD]
[TD]AT[/TD]
[/TR]
[TR]
[TD]676405140[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695589830[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695589841[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695618924[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695618390[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695580181[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695619285[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]695619521[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]006MSP34504901-39:05167[/TD]
[TD][/TD]
[TD]MSP34504[/TD]
[/TR]
</tbody>[/TABLE]