Jump to content
Macro Express Forums

Setting Variable


cyberchief
 Share

Recommended Posts

Hello all! This program just keeps getting better. I need to set a variable to a specific 2 digit code depending on the first 3 digits of a 13 digit number in a spreadsheet. Say the digits are 9705554444333, I need ME to see the the first three and if it equals 970, set another variable to equal CO (state code). I have 41 area codes to work with, but 9 state codes identified (multiple 3 digit codes can equal the same state code). Example, 970 and 303 all equal CO, but if 3 digits are 612, equal NO (along with others). To start, I am sure there will be a long line of code setting these variables, but I am trying to figure out where to start... how to get ME to see only the first 3 digits off of the clipboard. Any suggestions?

Link to comment
Share on other sites

HI all. I think from now on... I will wait until after I have done some research and playing around before posting dumb questions. I got it figured out... see below. Let me know if you have any suggestions to make this easier or to cut down on the code...

 

Activate Window: "CSR TEST"
Clipboard Copy
Variable Set String %T1% from Clipboard
Variable Modify String: Copy Part of %T1% to %T2%
If Variable %T2% = "970"
 OR
If Variable %T2% = "303"
 OR
If Variable %T2% = "307"
 OR
If Variable %T2% = "719"
 OR
If Variable %T2% = "720"
 Variable Set String %T3% "CO"
 Activate Window: "OSCAR_CO_WY - USWest Connect"
 Delay 0.2 Seconds
 Clipboard Paste
Else
 If Variable %T2% = "612"
   OR
 If Variable %T2% = "651"
   OR
 If Variable %T2% = "763"
   OR
 If Variable %T2% = "952"
   OR
 If Variable %T2% = "218"
   OR
 If Variable %T2% = "507"
   OR
 If Variable %T2% = "320"
   OR
 If Variable %T2% = "701"
   Variable Set String %T3% "NO"
   Activate Window: "OSCAR_EAST_NO - USWest Connect"
   Delay 0.2 Seconds
   Clipboard Paste
 Else
   If Variable %T2% = "308"
     OR
   If Variable %T2% = "319"
     OR
   If Variable %T2% = "402"
     OR
   If Variable %T2% = "515"
     OR
   If Variable %T2% = "563"
     OR
   If Variable %T2% = "605"
     OR
   If Variable %T2% = "641"
     OR
   If Variable %T2% = "712"
     Variable Set String %T3% "SO"
     Activate Window: "OSCAR_EAST_SO - USWest Connect"
     Delay 0.2 Seconds
     Clipboard Paste
   Else
     If Variable %T2% = "208"
       OR
     If Variable %T2% = "385"
       OR
     If Variable %T2% = "406"
       OR
     If Variable %T2% = "435"
       OR
     If Variable %T2% = "801"
       Variable Set String %T3% "UT"
       Activate Window: "OSCAR_AZ_ID_MT_NM_UT - USWest Connect"
       Delay 0.2 Seconds
       Clipboard Paste
     Else
       If Variable %T2% = "480"
         OR
       If Variable %T2% = "505"
         OR
       If Variable %T2% = "520"
         OR
       If Variable %T2% = "602"
         OR
       If Variable %T2% = "623"
         OR
       If Variable %T2% = "928"
         Variable Set String %T3% "NM"
         Activate Window: "OSCAR_AZ_ID_MT_NM_UT - USWest Connect"
         Clipboard Paste
       Else
         If Variable %T2% = "206"
           OR
         If Variable %T2% = "253"
           OR
         If Variable %T2% = "360"
           OR
         If Variable %T2% = "425"
           OR
         If Variable %T2% = "509"
           OR
         If Variable %T2% = "564"
           Variable Set String %T3% "WA"
           Activate Window: "OSCAR_WESTERN - USWest Connect"
           Delay 0.2 Seconds
           Clipboard Paste
         Else
           If Variable %T2% = "503"
             OR
           If Variable %T2% = "541"
             Variable Set String %T3% "OR"
             Activate Window: "OSCAR_WESTERN - USWest Connect"
             Delay 0.2 Seconds
             Clipboard Paste
           End If
         End If
       End If
     End If
   End If
 End If
End If
Text Type: <HOME>
Delay 0.5 Seconds
Text Type: <TAB><TAB><TAB><TAB><TAB><TAB>
Date: Type Out MM (03)
Date: Type Out YY (00)
Text Type: <TAB><TAB>
Text Type: %T3%
Delay 0.2 Seconds
Clear Text Variables: All
Text Type: <F5>
Delay 1 Seconds

Link to comment
Share on other sites

Let me know if you have any suggestions to make this easier or to cut down on the code...

One way to make the code simpler is to use the Switch / End Switch command construct.

Switch (T2)

  Case: 970

  Case: 303

  Case: 307

  Case: 719

  Case: 720

    Variable Set String %T3% "CO"

    Variable Set String %T4% "OSCAR_CO_WY"

  End Case

 

  Case: 612

  Case: 651

  Case: 763

  Case: 952

  Case: 218

  Case: 507

  Case: 320

  Case: 701

    Variable Set String %T3% "NO"

    Variable Set String %T4% "OSCAR_EAST_NO"

  End Case

 

  Case: 308

  Case: 319

  Case: 402

  Case: 515

  Case: 563

  Case: 605

  Case: 641

  Case: 712

    Variable Set String %T3% "SO"

    Variable Set String %T4% "OSCAR_EAST_SO"

  End Case

 

  Case: 208

  Case: 385

  Case: 406

  Case: 435

  Case: 801

    Variable Set String %T3% "UT"

    Variable Set String %T4% "OSCAR_AZ_ID_MT_NM_UT"

  End Case

 

  Case: 480

  Case: 505

  Case: 520

  Case: 602

  Case: 623

  Case: 928

    Variable Set String %T3% "NM"

    Variable Set String %T4% "OSCAR_AZ_ID_MT_NM_UT"

  End Case

 

  Case: 206

  Case: 253

  Case: 360

  Case: 425

  Case: 509

  Case: 564

    Variable Set String %T3% "WA"

    Variable Set String %T4% "OSCAR_WESTERN"

  End Case

 

  Case: 503

  Case: 541

    Variable Set String %T3% "OR"

    Variable Set String %T4% "OSCAR_WESTERN"

  End Case

End Switch

 

Activate Window: "%T4%"

Delay 0.2 Seconds

Clipboard Paste

There are a couple of things to point out. Notice how the target window title is placed in a variable T4. This allows the code to activate the window, delay, and paste from the clipboard to be moved outside the switch. Also, only the "unique" portion of each window title is needed.

 

This is only one way out of many to solve the problem.

Link to comment
Share on other sites

Hi again!

 

For my 2 penneth... I agree there are many ways; this is shorter, but perhaps not so easy to read or change?

Variable Set String %T5% "970 303 307 719 720 "

If Variable %T5% contains variable %T2%

  Variable Set String %T3% "CO"

  Variable Set String %T4% "OSCAR_CO_WY - USWest Connect"

End If

Variable Set String %T5% "612 651 763 952 218 507 320 701"

If Variable %T5% contains variable %T2%

  Variable Set String %T3% "NO"

  Variable Set String %T4% "OSCAR_EAST_NO - USWest Connect"

End If

// (don't need ELSE?; ALSO DO other if..end if s!

Activate Window: "%T4%"

Delay 0.2 Seconds

Clipboard Paste

What do you think, Floyd?

Randall

Link to comment
Share on other sites

Hmmm... much to think about. I am an old DB programmer... so I know much about the If/Then/Else statements of code. But don't know much about Switch/Case etc. I will look into these. The code works right now. Have few bugs to work out of the macro yet, but I am almost there. I will make a copy of what I got working right now and mess with the copy (so as not to lose all the work I did yesterday). Thanks for all the suggestions! I have a lot to learn.

Link to comment
Share on other sites

But don't know much about Switch/Case etc. I will look into these.

 

I would definitely recommend learning how to use the Switch/Case commands. Floyd certainly demonstrated that you certainly can eliminate many lines using them.

 

Thanks to Floyd for demonstrating how to use multiple case values to run one set of commands. I always tried AND/OR but never had much luck, so I had to duplicate my ME code for each case value. Not anymore though.

 

Kudos to randallc, I enjoy seeing code transformed into a leaner and meaner version.

Link to comment
Share on other sites

Re: Randall -What do you think, Floyd?
Yes, your way is the way that I would have done it for myself with two changes:
  1. Use the ELSE command to avoid forcing Macro Express to parse through strings again once it found the answer.
  2. Avoid using spaces within the string. Something like this: "970~303~307~719~720". It means very little in this example, but can be a nightmare when parsing untrimmed data strings.

Re: jowensii - Thanks to Floyd for demonstrating how to use multiple case values to run one set of commands. I always tried AND/OR but never had much luck, so I had to duplicate my ME code for each case value. Not anymore though.
There are limitations to the Switch/End Switch construct. The biggest is that each Case tests only for equality. It cannot, for example, be used for <, >, <>, or "contains". Just "=". Period. And if you are testing strings, they need to be the same length and case.

 

Stacking the Case statements (like in the example) does for the Switch command what OR does for the If command. Also, there is a Default Case command that, if used, will always be TRUE. This means that the commands you place in it will fire only if all previous Case statements are FALSE. It does for the Switch command what ELSE does for the If command.

 

If you are having problems with the boolean operators AND, OR, XOR then start another topic so you can get some help. They are very powerful and useful commands.

Link to comment
Share on other sites

There are a couple of things to point out. Notice how the target window title is placed in a variable T4. This allows the code to activate the window, delay, and paste from the clipboard to be moved outside the switch. also, only the "unique" portion of each window title is needed.

 

This is only one way out of many to solve the problem.

 

Actually, I noticed some of this earlier (after posting) and moved the clipboard paste outside of the if/or string. I am still looking at the switch arguements... I have the macro working for now (like a charm). code is about a mile long... but I am sure that with experience will come proficiency... and, either the code will get shorter, or the marcos I build will get bigger and better. lol

 

Thanks all for the help!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...