Jump to content
Macro Express Forums

gorilla

Members
  • Posts

    3
  • Joined

  • Last visited

Posts posted by gorilla

  1. 14 hours ago, rberq said:

    I think this approach will work:  Use Text File Begin Process, once only, to load all entries from the file into a single text variable.  That is, read the file one line at a time into T1, trim, and append its 9 characters to T2 (variable modify string/append), continue until the whole file has been loaded.  When done, T2 will be an array of 9-character strings.  (But you won’t need to process it as an array – see below.)  

    Next, copy the first spreadsheet cell into the clipboard, store in text variable T3.  Command
    If Variable %T2% contains "%T3%" 
    tells you whether the array (file) contains that cell’s data, and you can type or delete into other cells in the row as desired.  Text Type of arrow keys is good for moving from one spreadsheet cell to another.  Get this working for a single row of the spreadsheet.  Once you have it working for one row, add Repeat Start and Repeat End to handle all the rows one by one.  Use Repeat Exit when you reach the end of the spreadsheet (that is, when copy to clipboard finds nothing).  

     

    Here's my script which is inspired by your kind comment. Thanks a lot.

    Text File Begin Process: "list-a.txt"
     Repeat Until %T2% = %T1%
      Clipboard Copy
      Variable Set String %T2% from Clipboard
      Variable Modify String: Trim %T2%
       If Variable %T2% <> variable %T1%
        Text Type: <ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><HOME><ARROW DOWN>
       Else
        Text Type: <ARROW DOWN>
       End If
     Repeat End
    Text File End Process

    But There's 1 problem here.
    The last line of the "list-a.txt" is 2000-5592,
    and the last row of Excel is 2000-5599.

    This script ends at 2000-5592 and can't delete rows from 2000-5593 to 2000-5599.
    I need to delete data if the rows doens't match with the data in the list.

    I would appreciate it if you give me more help. :)

     

  2. Hello, 

    I'm here to ask help to make a Macro script what I'm struggling.
    Here's my situation.

    I have a Excel sheet like this and "A column" is important. Each row of "A column" contains a text string(XXXX-XXXX) and the total number of rows is 600. 

    A column        B col    C col    D col    E col

    1000-1500    text1    text2    number1    number2
    1000-1501    text1    text2    number1    number2
    ~
    1000-1599    text1    text2    number1    number2
    1000-1800    text1    text2    number1    number2
    1000-1801    text1    text2    number1    number2
    ~
    1000-1899    text1    text2    number1    number2
    1000-3500    text1    text2    number1    number2
    ~
    1000-3699    text1    text2    number1    number2
    2000-5400    text1    text2    number1    number2
    ~
    2000-5599    text1    text2    number1    number2

    And I have a text file "list-a.txt" which consists of roughly 300 lines and each line has a text string(XXXX-XXXX). 
    1000-1504
    1000-1507
    1000-1810
    2000-5411
    2000-5599

    What I need is to delete data in column (B:E) of a row in Excel if the text sting XXXX-XXXX is NOT in "list-a.txt".
    And I need to type/input data in column (B:E) if the text string XXXX-XXXX is IN "list-a.txt".

    For example,
    if "list-a.txt" file has only 5 lines as above.
    The Excel sheet has to be the same as the following.

    A column        B col    C col    D col    E col

    1000-1500
    1000-1501
    1000-1502
    1000-1503
    1000-1504    newT1    newT2    newN1    newN2
    1000-1505
    1000-1507    newT1    newT2    newN1    newN2
    1000-1508
    ~
    2000-5410
    2000-5411    newT1    newT2    newN1    newN2
    ~
    2000-5598
    2000-5599    newT1    newT2    newN1    newN2

    I used "Text File Begin Process" command from file "list-a.txt" and Set Variable to %T1%. And tried with many more combinations like "Repeat Until" command but all failed.
    Please help me out. 

    * Windows 7 and Macro Express v3.10.2.1

×
×
  • Create New...