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

Question

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.

Solution

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

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

Comments

[Admin]

Could you post a mockup of the data (a couple of rows) to understand you better?

[Admin]

Each file is an excel file correct? There is no quantity field at all in each excel file?

[Admin]

All the files are excel and they would look like this (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.

[Shaurya K]

Hey this is really close to what I need! I just need something else to happen when the string is found. All the files are excel and they would look like this (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. I would love to be able to do this myself but I'm currently in the process of learning VBA. Also how do you insert pictures in your comments?

[Eric F]

Then where I have "count = count + 1" put whatever that something else is there. That is the condition that is fired every time the string is found. (I don't know what your "something else" is). I do believe I answered your question at hand that you asked here though.

[Shaurya K]

updated my comment.

[Eric F]

@ShauryaK If it does answer your initial question please mark it as an answer and give it an upvote if it helped. If you need more help of course I will try to help in any way possible. :)

[Shaurya K]

Hey. I actually meant the value which is in the cell where 64 would be. If you see my picture 64 is just the quantity for this invoice. So It actually needs to be count + the number in the corresponsing quantity column

[Eric F]

ohh that is a bit more complicated... Is the quantity always so many cells to the right of where EDGE - Jungle Safari is found? I can't tell how many cells since your cells are merged. If this distance is consistent in all sheets then I will edit my answer to show you how to grab this value.

[Shaurya K]

Yes the quantity is actually always so many cells (3 in this case and 1 in the other, but that edit I'm fairly confident I can make) Also as you see here the same text "EDGE - Jungle Safari" appears twice. Would that be a problem? Can we add both those values? In the end I just need a total of the quantities. I don't need each quantity

[Shaurya K]

updated the question

[Eric F]

@ShauryaK I updated my answer. Take a look at my offset function which will return the value 3 cells to the right of the found text and then add them all together. (count = count + sh.Range(found.Address).Offset(0, 3).Value) I believe this does what you want.

[Shaurya K]

Thank you so much! Just one last question. What if I only want the count to trigger for the larger value. For example in imgur.com/L0anPQ6 the code you provided would add both 64 and 10 right. What if I wanted to only add 64 to the count? Basically per workbook only add the largest of the values found to the count.

[Eric F]

Oh boy this question keeps on growing! Give me a second and I will show you how to do that as well..

[Eric F]

@ShauryaK Updated again.

[Eric F]

@ShauryaK Did this help you? If so please mark it as an answer.

[Shaurya K]

Hey! Sorry for the late reply yesteray was a holiday here. I actually got the code working and it works really well! Two problems I had were, Firstly in the gif you attached it seems that cell A2 had to have the start directory and A3 had to have the string. But both of those values were hardcoded in the VBA Macro. I mean that putting the directory and the string in those cells did nothing, I had to edit them in the macro itself. Secondly whenever it tried closing the workbook it showed the save prompt which required manual intervention. I renamed it to wbk.Close savechanges:=False

[Shaurya K]

and it works now. Is there any way I can make it pick up the directory from cell A2 and the String from cell A3?

[Shaurya K]

I tried adding this to the code 'Dim strf As String strf = Worksheets("Sheet1").Cells(2, "B").Value For Each sh In wbk.Worksheets Set found = sh.Cells.Find(what:=strf, LookIn:=xlFormulas)' But it gave me an error: Runtime error '9' subscript out of range. What do I do to fix this?

[Eric F]

Just put directory =Range("'Sheet1'!A2").value for the first input and then later Set found = sh.Cells.Find(what:=range("'Sheet1'!B2").value, LookIn:=xlFormulas) . Range("'Sheet1'!A2").value represents a string so you can use it like any other string

[Eric F]

I purposely didn't make the reference in my code, so that you would look through and understand how it works. Usually on this site the question asker still has to do some of the work :)