stevecasper Posted October 22, 2008 Report Posted October 22, 2008 Hi, I've searched the boards and tried a few different things I've found, but nothing I've seen does what I'm trying to do. Here's the basic idea: In Excel I have a list of numbers in column A and another list of numbers in column B. I run a macro that searches for the specific number in column A and when it is found, the macro then takes the information on that line from column B. This much works fine. Here's where I have a problem: If there is no match in column A (the number searched for doesn't exist), I get a prompt for what should be in column B (The macro already knows what should be in column A based on the search). Now I want the macro to add the information from my variables to column A and column B. Here's what I have so far: Scripting Editor // For this example, 972 is not in my list yet. I need it to be. // The number needed in Column B is 801 (it is not there yet, either). Variable Set String %T3% "972" // The following Process puts Column A into T1 and Column B into T2, line by line. ASCII File Begin Process: "TestExcel.csv" (Comma Delimited Text ) If Variable %T1% = variable %T3% Break End If ASCII File End Process // If T1 never = T3, then T3 is not yet in my list If Variable %T1% <> variable %T3% Variable Set String %T2% "" Variable Set String %T2% from Prompt // Right here I want to be able to append T3 to Column A and T2 to Column B. // So that next time I search for T3 I won't have to input T2 by hand again. End If Variable Modify String: Append %T3% to %T2% Variable Set String %T3% "%T2%" Text Box Display: T3 Direct Editor Script <REM2:For this example, 972 is not in my list yet. I need it to be.><REM2:The number needed in Column B is 801 (it is not there yet, either).><TVAR2:03:01:972><REM2:The following Process puts Column A into T1 and Column B into T2, line by line.><ADFBEG:F10:001:000001:000000:C:\TestExcel.csv><IFVAR2:4:01:1:T3><BREAK><ENDIF><ADFEND><REM2:If T1 never = T3, then T3 is not yet in my list><IFVAR2:4:01:2:T3><TVAR2:02:01:><TVAR2:02:02:FWhat number goes with %T3%?FFCenter:Center><REM2:Right here I want to be able to append T3 to Column A and T2 to Column B.><REM2:So that next time I search for T3 I won't have to input T2 by hand again.><ENDIF><TMVAR2:08:02:03:000:000:><TVAR2:03:01:%T2%><TBOX4:T:1:CenterCenter000278000200:000:T3%T3%> If more information is needed, or if I've been unclear about anything, please let me know. Thanks! Quote
stevecasper Posted October 22, 2008 Author Report Posted October 22, 2008 Well, I guess I'm just impatient After much trial and error, and no immediate response, I think I've figured out how to do it. I don't know if it's the most elegant way (though it's better than a lot of my earlier attempts) so if anybody knows or thinks of a better way to do this, please let me know. For anybody who is looking to do the same thing (or something similar), here's what I did: Scripting Editor: Variable Set String %T3% "972" // Here I create a .txt file with my .csv data (the information ends up separated by a comma) If File Exists "TestExcel.csv" Copy File or Files: "TestExcel.csv" End If // The following Process puts Column A into T1 and Column B into T2, line by line. ASCII File Begin Process: "TestExcel.csv" (Comma Delimited Text ) If Variable %T1% = variable %T3% Break End If ASCII File End Process If Variable %T1% <> variable %T3% Variable Set String %T4% "" Variable Set String %T4% from Prompt Variable Set String %T2% "%T4%" Variable Modify String: Append ",%T4%" to %T3% Variable Modify String: Append %T4% to Text File // Here I replace my .csv file with the .txt file where I just appended the new info. Copy File or Files: "TestExcel.txt" End If Direct Editor: <TVAR2:03:01:972><REM2:Here I create a .txt file with my .csv data (the information ends up separated by a comma)><IFOTH:01:2:C:\TestExcel.csv><DOFILE:05:NN:C:\TestExcel.csv>C:\TestExcel.txt><ENDIF><REM2:The following Process puts Column A into T1 and Column B into T2, line by line.><ADFBEG:F10:001:000001:000000:C:\TestExcel.csv><IFVAR2:4:01:1:T3><BREAK><ENDIF><ADFEND><IFVAR2:4:01:2:T3><TVAR2:04:01:><TVAR2:04:02:FWhat is the correct number for Column B?FFCenter:Center><TVAR2:02:01:%T4%><TMVAR2:07:03:00:000:000:,%T4%><TMVAR2:20:04:00:000:000:C:\TestExcel.txtF><REM2:Here I replace my .csv file with the .txt file where I just appended the new info.><DOFILE:05:NN:C:\TestExcel.txt>C:\TestExcel.csv><ENDIF> Quote
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.