cyberchief Posted March 21, 2005 Report Share Posted March 21, 2005 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? Quote Link to comment Share on other sites More sharing options...
cyberchief Posted March 21, 2005 Author Report Share Posted March 21, 2005 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 Quote Link to comment Share on other sites More sharing options...
floyd Posted March 21, 2005 Report Share Posted March 21, 2005 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. Quote Link to comment Share on other sites More sharing options...
randallc Posted March 22, 2005 Report Share Posted March 22, 2005 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 Quote Link to comment Share on other sites More sharing options...
cyberchief Posted March 22, 2005 Author Report Share Posted March 22, 2005 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. Quote Link to comment Share on other sites More sharing options...
jowensii Posted March 22, 2005 Report Share Posted March 22, 2005 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. Quote Link to comment Share on other sites More sharing options...
floyd Posted March 22, 2005 Report Share Posted March 22, 2005 Re: Randall -What do you think, Floyd?Yes, your way is the way that I would have done it for myself with two changes:Use the ELSE command to avoid forcing Macro Express to parse through strings again once it found the answer. 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. Quote Link to comment Share on other sites More sharing options...
cyberchief Posted March 22, 2005 Author Report Share Posted March 22, 2005 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.