Compare cell.Value in different Workbooks, and do things

[Peteor]

This macro has me stumped.

I have a column of information in Workbook "A", Worksheet 1, Column "C" AND I have a column of information in Workbook "B", Worksheet 1, Column "D" which correspond with one another.

I would like to read-in the cell.Value For each cell in Range("C:C") of workbook A;
Compare to all of the cell.Values in Range("D:D") of Workbook B;
If a match is found, Copy the information in column J of the respective row of Workbook B;
Paste the information to column I of the respective row of workbook A;

I am most confused with the "compare" operation I am stating above. I know how to use a For Each loop to analyze all of the cells in a range of any given spreadsheet, but not how to compare with another For Each loop of another Worksheet. Is there a better way to accomplish this?

I am also confused with how to run the comparison between the Activesheets/loops, and not slow the program down too much.

 

[mumps]

Make sure both workbooks are open. Copy/paste the macro below in a standard module in WorkbookA and run it from there. Change the workbook name (in red) to suit your needs.

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, srcWS As Worksheet, desWS As Worksheet, fnd As Range
    Set srcWS = Workbooks("[COLOR="#FF0000"]WorkbookB.xlsx[/COLOR]").Sheets("Sheet1")
    Set desWS = ThisWorkbook.Sheets("Sheet1")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWS.Range("D2", srcWS.Range("D" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each Rng In desWS.Range("C2", desWS.Range("C" & desWS.Rows.Count).End(xlUp))
        If RngList.Exists(Rng.Value) Then
            Set fnd = srcWS.Range("D:D").Find(Rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                desWS.Cells(Rng.Row, 9) = fnd.Offset(0, 6)
            End If
        End If
    Next Rng
    Application.ScreenUpdating = True
End Sub

 

[Peteor]

Holy smokes! I set my Book and sheet names, clicked go, and it was done. Beautiful macro, which I will be saving to use in the future. It worked 100% as intended, Thank you!

 

[mumps]

You are very welcome.

 

[KReinke]

Hello together,
I came across this forum post via the internet while searching for a solution to my VBA problem and immediately created a profile.

My problem is a similar one to the post above.

I would like to create an Overview Excel file that allows me to define specific filters and subsequently search various standardized workbooks and predefined cells for meeting the defined criteria and then copy specific values from the workbooks that meet all the filter criteria.

For example, I have a workbook for each product of an online merchant.

In the overview sheet I then select the filters:
Sales volume of the last 12 months > 10.000
Price > 30$
Industry = Textile.

Now I want to put a macro in the overview file, which when pressed opens the file browser and lets me select a specific directory. The macro will then search all standardized Excel files (each Excel file contains information about a product) in this directory to see if they meet the filter criteria. If a product meets all the set criteria, the most important key figures should be copied from the respective workbook to the overview file.

The whole thing will probably be insanely complicated, especially since I'm a VBA amateur.

Still, I was hoping that some of you might have some initial helpful food for thought.

Best regards

K.R.

 

[mumps]

Hello and welcome to the Forum. I would suggest that to increase the chances of getting a response, you start your own new thread. You could post a link to this thread if you think it is useful. Also, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your data sheets.

 

[KReinke]

Hello and welcome to the Forum. I would suggest that to increase the chances of getting a response, you start your own new thread. You could post a link to this thread if you think it is useful. Also, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your data sheets.

Hey Mumps, thanks for your quick reply.
I followed your suggestion to create a new post already, however, I have not included Screenshots so far, which I will correct later on.

Have a great day and best regards.