Help! Unpredictable Results With Excel

[tdelios]

I am trying to run a hotkey that will copy the value into a variable from a excel cell.. perform a 'IF End' test function and the test function will replace the abbrivation of whatever the variable contains and then write it back to the cell..

 

Other than my 'AIR CANADA' and BANK OF NOVA SCOTIA' cells, the macro is giving me unpredictable results. There are times where it does replace the cell at all and at times will replace the cell with something that is not even in any of my test conditions. If I open up notepad and highlight the same words and run the macro, it behaves fine. Below is my script:

 

<DIS:<CLIPE><CLIPC><TVAR2:01:03:><TMVAR2:01:01:00:000:000:><IFVAR2:1:01:1:AIR CANADA><TMVAR2:21:01:00:000:000:AIR CANADAAC><ENDIF><IFVAR2:1:01:1:ABB INC.><TMVAR2:21:01:00:000:000:ABB INC.ABB><ENDIF><IFVAR2:1:01:1:ABB CANADA><TMVAR2:21:01:00:000:000:ABB CANADAABB><ENDIF><IFVAR2:1:01:1:BANK OF NOVA SCOTIA><TMVAR2:21:01:00:000:000:BANK OF NOVA SCOTIABNS><ENDIF><IFVAR2:1:01:1:ALBERTA EMPLOYMENT, IMMIGRATION AND INDUSTRY><TMVAR2:21:01:00:000:000:ALBERTA EMPLOYMENT, IMMIGRATION AND INDUSTRYAEII><ENDIF><IFVAR2:1:01:1:ALBERTA HEALTH AND WELLNESS><TMVAR2:21:01:00:000:000:ALBERTA HEALTH AND WELLNESSAHW><ENDIF><IFVAR2:1:01:1:AMERICAN EXPRESS - CANADA><TMVAR2:21:01:00:000:000:AMERICAN EXPRESS - CANADAAMEX><ENDIF><IFVAR2:1:01:1:ATB FINANCIAL - ALBERTA TREASURY BRANCHES><TMVAR2:21:01:00:000:000:ATB FINANCIAL - ALBERTA TREASURY BRANCHESATB><ENDIF><IFVAR2:1:01:1:Banque Nationale Du Canada (BNC)><TMVAR2:21:01:00:000:000:Banque Nationale Du Canada (BNC)BNC><ENDIF><IFVAR2:1:01:1:BELL CANADA><TMVAR2:21:01:00:000:000:BELL CANADABELL><ENDIF><TEXTTYPE:%T1%><TEXTTYPE:<ARROW DOWN>>

 

 

any ideas would be greatly appricated..

[stan]

Here is my guess as to what is happening. When you copy a cell to the clipboard, Excel adds hidden extra characters to the clipboard. After you save the clipboard to a variable, use the Variable Modify String command - Trim option. This removes the extra characters so that only the visible characters remain in the variable.

[cmharrington]

I've run into the same probelm, I would also add a Strop CR/LF as well as the Trim.

 

<TMVAR2:01:01:00:000:000:><TMVAR2:04:01:00:000:000:>

[monpasdg]

I am trying to run a hotkey that will copy the value into a variable from a excel cell.. perform a 'IF End' test function and the test function will replace the abbrivation of whatever the variable contains and then write it back to the cell..

 

Other than my 'AIR CANADA' and BANK OF NOVA SCOTIA' cells, the macro is giving me unpredictable results. There are times where it does replace the cell at all and at times will replace the cell with something that is not even in any of my test conditions. If I open up notepad and highlight the same words and run the macro, it behaves fine. Below is my script:

 

any ideas would be greatly appricated..

 

 

You can try hitting the F2 function key which puts the cursor on the inside of the cell. Then just have the macro select the contents, copy and type the resulting information into the cell

 

Code:

 

<REM2:Select the Cell><TEXTTYPE:<F2><END><SHIFT><HOME>><REM2:><REM2:><REM2:+=--=+=--=+=--=+=Clip Board Copy+=--=+=--=+=--=+=><REP3:01:000001:000001:00005:0:01:><CLIPC><REM2:><REM2:Wait 1.3 Seconds for Clipboard><REP3:01:000001:000001:00087:0:01:><IFOTH2:07:1:><MSD:15><ELSE><EXITREP><ENDIF><ENDREP><REM2:><IFOTH2:07:1:><REM2:If the wait for 1.3 seconds did not capture anything, reinitiate the <Ctrl + C>><MSD:15><ELSE><EXITREP><ENDIF><ENDREP><REM2:><REM2:Run the IF-End Code here><REM2:><REM2:><REM2:><REM2:><REM2:><REM2:Run the IF-End Code here><REM2:><REM2:Text Type: Resulting Abbrivation>

 

I use a more advance clipboard copy because every computer (generally) has a different about of time between hitting <Ctrl + C> and when the data actually gets to the clipboard. Without the code above sometimes setting a variable right after a clipboard copy could be blank, depending on the clipboard copy speed of the computer. I think the default for a computer is 250ms .

 

Also I saw a Clipboard Empty in your code, I use excel 2004 and if a cell is copied (has the dots around the cell) clipboard empty crashes excel everytime.

[RaechelParker]

Very nice post with a ton of informative information. I really appreciate the fact that you approach these topics from a stand point of knowledge and information

instead of the typical “I think” mentality that you see so much on the internet these days.

[monpasdg]

What about using the Find And Replace inside excel?

 

You could make the macro type in the fields what you are looking for what to replace it with then press <Alt> + A and it will replace all of the cells that match that criteria.

 

All the fields that you need to use can be activated using short cut keys.

 

For example:

 

<Ctrl + H> //Open Find and replace

 

 

<Alt + N> // Put cursor in the "find what" field

Type: AIR CANADA

<Tab> or <Alt + E> // put cursor in the "Replace with field

Type: AC

<Alt + A> // Replace All

 

 

 

 

-=+=--=+=--=+=--=+=-

For added complexity you can create a text file that is Ascii delimited (I saw that some of your items have commas in the name which will messup any comma delimted files ex:Line 4):

 

 

File Contents:

ln1: "Air Canada","AC"

ln2: "ABB INC.","ABB"

ln3: "ABB CANADA","ABB"

ln4: "ALBERTA EMPLOYMENT, IMMIGRATION AND INDUSTRY","AEII"

 

 

<Ctrl + H> //Open Find and replace

Ascii File Begin Process

//T1 Column 1

//T2 Column 2

 

<Alt + N> // Put cursor in the "find what" field

Type: %T1%

<Tab> or <Alt + E> // put cursor in the "Replace with field

Type: %T2%

<Alt + A> // Replace All

 

Ascii File End Process