Macro-Search Data for ‘Like’ items (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 REG2: but also some lines with REG:
Do you only want to use the lines with REG2: as you indicated above, or also those with REG: ?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote