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


    Quote from Sweater Fish Delux

    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?


    This will find first match. If there is more than one, search the help for FindNext.

  • 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


    Try inserting this code into your existing code.

  • 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:


    Code
    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: Conditional Importing Of External Cells


    What is value of DestColumn? If it is a cell at the bottom of your sheet, your code should work. Otherwise, I assume that is your problem.

  • 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


    Works fine when I test it. Are you sure it's not finding anything? Are you resetting the value of rFound to nothing? Are you perhaps finding a match in column B, but there is no data in column C?

  • 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:


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


    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.

    Code
    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.

  • Re: Find Cell Value In Another Workbook & Copy


    Quote from Sweater Fish Delux

    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.


    I think it should work either way.

    Quote from Sweater Fish Delux

    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?


    It is set to the empty cell in Column C. What do you want to happen if "Application ID" is found in column B but column C is blank?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!