Excel – How to Search multiple files for value corresponding to text in same row in Excel

microsoft excelmicrosoft-excel-2007microsoft-excel-2010vbaworksheet-function

I know this is a blatant help me post, but I have been given this task and I don't know how to get it done in time.

I have moderate knowledge of excel, but I dont know any VBA. I have learnt C++ so I do understand code and especially the logic behind code.

The task that needs to be done is: We are a small startup that sells educational toys. A total of 8 products. So apparently no one was keeping a tally of our goods sold item wise. We have over 200 invoices that list each transaction. Now I need a way to search through all of these files for a text string eg "superuser" and then give me me the number in the column next to it for a certain format of invoices and in the column 3 to the right(column index number 4 if using vlookup) for another format. Yes we have two formats for some reason. I can separate the two formats into different folders.

Is there any way to do this without manually opening each file and then keeping a tally?

dit: All the files are excel and they would look like this http://imgur.com/L0anPQ6 (this is unfortunately a pdf export, I cannot access the original currently) Picture What I need is if the string to be found is EDGE – Jungle Safari, then I need the value 64 and the value 10. Basically the value under QTY in the corresponding row.

Best Answer

Initial setup

I have built a small example that should get you what you are looking for. First I set up a few excel files in a single folder where some contain the string "abc" and some don't. I put some that have more than just "abc" and in different sheets within the same workbook. This should simulate exactly what you have.

Showing you how it works once completed

enter image description here

You must supply the initial directory like I have in cell A2.

The coding

We initially set up a loop to go through all files that are excel files with this line:

fileName = Dir(directory & "*.xl??")

As each file name is found, we open it using this:

Dim wbk As Workbook
With wbk
Set wbk = Workbooks.Open(directory & fileName)
End With

With each workbook open we use the cells.find command to search for our string and do this through each sheet. If there is a match, then we increase our variable counter by one:

For Each sh In wbk.Worksheets
    Set found = sh.Cells.Find(what:="abc", LookIn:=xlFormulas)

    If Not found Is Nothing Then
       sh.Activate
       found.Select
       count = count + 1
       Else
    End If
Next sh

Lastly, make sure to close each workbook before going to the next, so we don't have a memory issue of having too many workbooks open:

wbk.Close

Here is all of the code put together:

Sub LoopThroughFiles()
Range("'Sheet1'!A6:A10000").ClearContents

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
directory = "C:\Users\wzcj9x\Desktop\New folder\"
fileName = Dir(directory & "*.xl??")



i = 5
Do While fileName <> ""

i = i + 1

If fileName <> "" Then
Dim wbk As Workbook
With wbk
Set wbk = Workbooks.Open(directory & fileName)
End With


Dim sh As Worksheet
Dim found As Range
Dim count As Integer

For Each sh In wbk.Worksheets
    Set found = sh.Cells.Find(what:="abc", LookIn:=xlFormulas)

    If Not found Is Nothing Then
       sh.Activate
       found.Select
        count = count + sh.Range(found.Address).Offset(0, 3).Value
       Else
    End If
Next sh
wbk.Close
End If



fileName = Dir()

Loop

Range("'Sheet1'!C2").Value = count

End Sub

You simply need to run the macro and it will produce a count for you. If you only want to produce a count of 1 per workbook, regardless how many times it exists in the workbook, it should be very easy to modify my count method.

Let me know if you have any questions but hopefully this helps to get you down the right path at least.

Update

I updated my question to return the value that is 3 cells to the right of the found cell and add those all together. This matches the question asker's new update.

Another Update based on comments

I modified my code to only sum up the maximum value that meets the criteria per workbook and then add only those values together:

Dim max As Integer

max = 0

For Each sh In wbk.Worksheets

    Set found = sh.Cells.Find(what:="abc", LookIn:=xlFormulas)

    If Not found Is Nothing Then
       sh.Activate
       found.Select

       If sh.Range(found.Address).Offset(0, 3).Value > max Then
       max = sh.Range(found.Address).Offset(0, 3).Value
       End If

       Else
    End If
Next sh

      count = count + max

wbk.Close
Related Question