Find Cell Value In Another Workbook & Copy

What I have is a collection of about 500 .xls files that I want to import into a single worksheet in a new workbook, but I only want to bring in specific cells from the source sheets which are unfortunately not always in the same row numbers.

I have found this link on loops:
http://www.ozgrid.com/VBA/loop-through.htm
Which looks like it will do the work of digging through each workbook in a certain folder, but do I need to further specify a sheet within the workbook once it's opened in that loop code? Even if there's only one sheet in each source workbook? The sheets are named differently in each workbook, so that may be a problem.

Now the really complicated bit (I think) is the conditional stuff. Each source sheet is three columns (A, B and C) and I want to import the string (some will be numbers, some will be text) from the C column to a single row of the destination sheet, but only if the string in the source's B column matches what I'm looking for. There will actually be a few dozen of these conditional searches based on different strings from the B column, but if someone could just show me how to do it for a single string, I can expand that to all the other searches I need to do.

Let's say the search term is 'Application ID,' how do I look through the source worksheet for that string in column B and if I find it bring the string from column C on the same row into my destination sheet?

I hope that's clear enough. Thanks in advance for any help.

Re: Conditional Importing Of External Cells

Thanks for your help, but I can't get this working. I should have mentioned that I don't have any experience with VBA coding.

If I keep this as a separate sub routine and just call it from the directory searching routine, I get a message box (one for each source file) saying "not found." If I try to incorporate your code into the existing routine at the appropriate location, I get a data out of range error.

I think these problems are related to the "SourceWB" and "DestWB" designations in your code. Are these supposed to be variable names that were defined elsewhere?

In the directory searching code I mentioned above that I'm using, the variables used are wbResults (this will be the source) and wbCodeBook (this will be the destination), so I tried inserting those variable names into your code in place of the SourceWB and DestWB? For the worksheets, I've replaced both "SourceWS" and "DestWS" with 1 (no quotes) since I'm only concerned with the first sheet in both the source and destination. I also changed "DestRng" to "3:3." However, even with these changes, I get the same errors mentioned above.

Are there other changes I'm supposed to be making in order to make this code work together with the loop-through code here http://www.ozgrid.com/VBA/loop-through.htm

Or am I going about this all wrong. As I said, I'm not at all familiar with VBA.

Re: Conditional Importing Of External Cells

Excellent! This works very well and I've managed to easily get it working to extract all the different cells I need and put them where I want them. Thanks so much.

Even though it doesn't seem to be needed with the current group of files I'm working with, I tried making this change just in case I use this script again at some point in the future:

If rFound Is Nothing Then 
        wbCodeBook.Worksheets(1).Range(DestColumn).End(xlUp).Offset(1, 0) = "n/a" 
    Else 
        wbCodeBook.Worksheets(1).Range(DestColumn).End(xlUp).Offset(1, 0) = rFound 
    End If

But it doesn't work as I expected. With or without that change, if the the search string is not found, nothing is placed in the current cell until the next file is opened (and hence all the subsequent rows of that column could be misaligned). I'd like some sort of dummy value or n/a or something like that to be placed in the current destination cell if the search string isn't found in the source file. Any hints on that?

Thanks so much again for your help on this.

Re: Find Cell Value In Another Workbook & Copy

destColumn is just a string that I specify when I call the CustomFind sub routine from within the main directory searching routine. I pass the current source and destination workbook variables and a search string as well as the destination column. For the destColumn value, I just use something like "A65536" but change the initial letter depending on which column I want the rFound value to be copied to. This works fine when the search string is found in the source file, but does nothing at all if it's not found.

Re: Find Cell Value In Another Workbook & Copy

Hm, pasting "n/a" when the search string isn't found seems to work when the search code is embedded in the same routine as the directory looping code, but it doesn't work when the search code is a separate subroutine.

So the following code works as desired:

Sub RunCodeOnAllXLSFiles()


Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook
Dim rFound As Range


On Error Resume Next


Set wbDestiny = ThisWorkbook


With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Licenses"
    .FileType = msoFileTypeExcelWorkbooks


    If .Execute > 0 Then 'Workbooks in folder
        For lCount = 1 To .FoundFiles.Count 'Loop through all.
            Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)


            Set rFound = Nothing
            Set rFound = wbSource.Worksheets(1).Range("B:B").Find _
            (What:="Application Id", LookIn:=xlValues, _
            LookAt:=xlWhole, SearchDirection:=xlNext).Offset(0, 1)


            If rFound Is Nothing Then
                wbDestiny.Worksheets(1).Range("B65536").End(xlUp).Offset(1, 0) = "n/a"
            Else
                wbDestiny.Worksheets(1).Range("B65536").End(xlUp).Offset(1, 0) = rFound
            End If


            wbSource.Close SaveChanges:=False


            Next lCount
    End If
    
End With
    
On Error GoTo 0


End Sub

But this code does not--it leaves the cell empty if the search string is not found:

Sub RunCodeOnAllXLSFiles()


Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook


On Error Resume Next


Set wbDestiny = ThisWorkbook


With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Licenses"
    .FileType = msoFileTypeExcelWorkbooks


    If .Execute > 0 Then 'Workbooks in folder
        For lCount = 1 To .FoundFiles.Count 'Loop through all.
            Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)


            Call CustomFind(wbSource, "Application Id", wbDestiny, "B65536")


            wbSource.Close SaveChanges:=False


            Next lCount
    End If
    
End With
    
On Error GoTo 0


End Sub


Sub CustomFind(wbSource As Workbook, rSearch As String, wbDestiny As Workbook, colDestiny As String)


Dim rFound As Range


Set rFound = Nothing
Set rFound = wbSource.Worksheets(1).Range("B:B").Find _
(What:=rSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlNext).Offset(0, 1)


On Error GoTo 0


If rFound Is Nothing Then
    wbDestiny.Worksheets(1).Range(colDestiny).End(xlUp).Offset(1, 0) = "n/a"
Else
    wbDestiny.Worksheets(1).Range(colDestiny).End(xlUp).Offset(1, 0) = rFound
End If


End Sub

I guess this just points to some fundamental misunderstanding of VBA on my part since I can't figure out any reason why the second code doesn't work as desired.

Also, you mentioned the possibility of an empty cell in column C where I'm trying to retrieve data. How could I catch those occurances as well? rFound doesn't remain set to Nothing in those cases? What is it set to?

I'm also trying to get the FindNext function to work as you suggested above, but inserting the following code into the working piece of code posted above always yields "n/a" in column C even when there is a second occurance of the search term.

Set rFound = Nothing
Set rFound = wbSource.Worksheets(1).Range("B:B").FindNext(rFound).Offset(0, 1)


If rFound Is Nothing Then
    wbDestiny.Worksheets(1).Range("C65536").End(xlUp).Offset(1, 0) = "n/a"
Else
    wbDestiny.Worksheets(1).Range("C65536").End(xlUp).Offset(1, 0) = rFound
End If

Not resetting rFound to Nothing here just pastes the same value into columns B and C even if the second one should be different.

I'm sorry to keep coming back with more problems, but I'm afraid I just don't understand any of this well enough to work it out myself despite reading many descriptions of how it should work and spending hours changing things here and there trying to get it working.