Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro-Search Data for 'Like' items (Excel 2002)

    Hi!
    I've got the following code that I've used for another worksheet that I'd like to adapt to a new worksheet... instead of having the code below search column A for data that contains an "S", I'd like to search column A and look for items that "contain" the abbreviations Mon, Tues, Wed, Thur, Fri, TOT, ACCT and if it does then I'd like to DELETE the entire COLUMN (as opposed to clearing the contents). As usual, any help with tweaking the code below would be greatly appreciated.
    Thanks!!
    Lana

    'Delete blank and unwanted rows from the data
    With Worksheets("Data")
    For lngRow = 1 To .Range("A65536").End(xlUp).Row
    If UCase(.Range("A" & lngRow)) = "S" Then
    .Range("T" & lngRow & ":AB" & lngRow).ClearContents
    End If
    Next lngRow
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Er, if you search through column A, find one of the abbreviations, then delete column A, there is nothing left to search for. Is that really what you intended?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    I get your point... sounds silly, however I've got this darn database from ADP (payroll system) that believe it or not, according to ADP, can NOT be downloaded into Excel! Anyway, it's report in a notepad file... several pieces of data show up in ONE line and it's all in column A. I've tried the text to columns, delimited and fixed width to no avail. The only options it to omit the useless data in order to narrow it down to the few little pieces of data I'm truly after. After I've omitted the useless data I plan to use the mid formula to separate and/or extract the data from column A in an organized/useable manner. Hope that makes sense. Below is how the data actually looks when I open the notepad file in Excel. I'm going to extract the 10 digit employee number and the 6 digit home dept from the first line, and then the REG2: hours on the subtotal down lower.

    CLOSE,JOE 0003000275 01MA04
    ID IN Dept ACTIVITY OUT ID IN Dept ACTIVITY OUT TOTALS
    Mon 01/29 1159p 635a M 704a 100p*l 12.50 12.50
    Tue 01/30 1158p 630a M 658a 934a*l 9.07 21.57
    Change Punch 26 01/30 100p
    01/30 100p
    Change Punch 26 01/31 934a
    01/31 934a


    Acct:01MA04
    REG2: 21.57




    BONNOLLI,JACK M 0003000186 01MA04
    ID IN Dept ACTIVITY OUT ID IN Dept ACTIVITY OUT TOTALS
    Mon 01/29 355p 852p M 921p 1230a 8.00 8.00
    Tue 01/30 355p 858p M 927p 1230a 8.00 16.00
    Wed 01/31 355p 856p M 925p 1230a 8.00 24.00

    Acct:01MA04
    REG2: 24.00

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    I'd parse the text file. I have attached an attempt to do so based on the data you posted. It will no doubt have to be fine-tuned. Copy the code into a module in a new workbook, save the workbook, then run the macro to see whether it does what you want.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Hi Hans... I'm sorry to say that I don't know what parse means... plus I'm unsure what I should do with the code... where do I put it? I only have limited knowledge of how to use the macros in Excel. Am I to add this code to the notepad file?
    Thanks,
    Lana

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Right-click the attachment in my previous reply.
    Select 'Save target as' from the popup menu.
    Save the file to a convenient location.

    Create a new workbook.
    Press Alt+F11 to activate the Visual Basic Editor.
    Select File | Import File...
    Select 'All Files' in the File types dropdown list.
    Navigate to the downloaded text file and open it.

    Switch back to Excel (Alt+F11).
    Save the workbook.
    Press Alt+F8 to open the Macros dialog.
    Select the Import macro, then click Run.

    You'll be prompted to open a file.
    Navigate to the Notepad file and open it.
    If all goes well, some values from the file will be inserted into the workbook.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    BTW, 'to parse' means to break down a text string into parts.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Hi Hans,
    I copied & pasted the code into a module in an Excel workbook... I ran the macro... it prompted me to open a file... I opened the notepad file, and in cell A1 the number 1 was the result of running the macro. There is no other data located in the Excel file. I've attached an example of the notepad file for your reference.
    Thanks!
    Lana
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    I warned you that the code would have to be fine-tuned. This file looks a bit different from what you originally posted. One of the things I notice is that there are some lines with <code>REG2:</code> but also some lines with <code>REG:</code>
    Do you only want to use the lines with <code>REG2:</code> as you indicated above, or also those with <code>REG:</code> ?

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Hi Hans,
    Yes you warned me it needed tweaking... trust me I wasn't complaining!!! I appreciate all the help you give me! Anyway, I've gone back to the drawing board with our payroll person, and of course, still to no avail, the data can only be exported to a notepad file.... HOWEVER, we were able to get actual WAGE data as opposed to the HOURS that were in the earlier report. This is good news as then no further calculation is needed after we convert the notepad file into an Excel file. Sooo, back to my original request, but with a slightly different data set then before. I've attached an Excel file with the notepad data copied & pasted into it. In reality it's about 5,000 lines long, however I've omitted the individual peoples actual wage data for example purposes. This report does subtotal the wages by dept at the end of the report, which is really what I'm after... in this example this report break shows up on row 52 as "Report summary totals:". I'd like to write a macro to "go to" the data listed AFTER that "Report summary totals:" line and then extract the 6 digit home department number (for example in row 54 it's 01MA04)... then I'd like it to find the wages for that department, which is located, in this example, after the words "Wages summed" on row 58. I'm really only interested in the home department code and it's related dollar amount. I could throw some formulas in Excel to extract the data from column A (using the mid formula), however then there's all the blank rows and the fact that the home department wouldn't be in the same row as the amount... if they are in the same row, the it would make it a whole lot easier to attach a vlookup formula so I can post these accruals to the proper general ledger accounts.
    Thanks for bearing with me Hans!
    Lana
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    I still think it's better to parse (process) the Notepad file and extract the info you need.

    Right-click the attachment in this reply.
    Select 'Save target as' from the popup menu.
    Save the file to a convenient location.

    Create a new workbook.
    Press Alt+F11 to activate the Visual Basic Editor.
    Select File | Import File...
    Select 'All Files' in the File types dropdown list.
    Navigate to the downloaded text file and open it.

    Switch back to Excel (Alt+F11).
    Save the workbook.
    Press Alt+F8 to open the Macros dialog.
    Select the Import macro, then click Run.

    You'll be prompted to open a file.
    Navigate to the Notepad file and open it.
    If all goes well, values from the file will be inserted into the workbook.
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Of course this worked perfect Hans! I can't thank you enough... you are awesome! I've reviewed the code in hopes of understanding bits and pieces of it so that I can learn from it and possibly apply a modified version of this macro to other ideas I've got swimming around in my head... anyway, I'm wondering how this macro really works... the code looks a little different than what I'm used to seeing.... VBA must work with a lot of different softwares??? Obviously it's very useful in Excel, and now I can see it can read info from a notepad file, and I'm thinking it works for Microsoft Word as well... does it work well with Access? I have a project that I started working on long ago, and I'm pretty sure I'll need to download my trial balance detail into Access as opposed to Excel due to the 65,536 line limitiation and I want to apply a vlookup concept to the data dumped into Access and then use Excel Pivot tables to read the data from Access... I'm wondering if a macro will work in applying a vlookup formula and adding the additional data to the Access database?
    Thanks again Hans!!
    Lana

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-Search Data for 'Like' items (Excel 2002)

    Hi Lana,

    Most of the code is independent of Excel. Only the two lines that populate cells in column A and B:

    Range("A" & r) = "'" & Mid(strLine, p1 + 1)

    and

    Range("B" & r) = Mid(strLine, p1 + 1)

    are specific for Excel. The rest is generic VBA that can be used in Word and Access too.

    The "core" of the code uses instructions dating back to the first versions of BASIC to process a text file. The line

    Open strFile For Input As #f

    opens the file in the computer's memory (not in a window). Next, there is a loop that reads the lines of text one by one until the text "REPORT SUMMARY TOTALS" is encountered or the file ends:

    Do
    Line Input #f, strLine
    Loop Until EOF(f) Or InStr(1, strLine, "REPORT SUMMARY TOTALS", vbTextCompare) > 0

    Line Input reads a line from the file, and the EOF (End Of File) function returns True as soon as the end of the file is reached.

    This is followed by another loop that looks for lines containing "DEPT:" (to extract the department code from) and lines containing "WAGES SUMMED" (for the amount).

    Finally, the file is closed again (i.e. removed from computer memory, not from disk).

    The code could easily be adapted to add records to a table in Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •