Help a Newbie with a not so simple VBA Macro

[FedieSlvetr]

Hi Guys,

I need a program that can read multiple columns from multiple spreadsheets and carries out an action if it matches.
I believe though the problem is a bit too complex for vlookup!

I need a vba macro that can read the a common column from two spreadsheets to see if they match. Then for every match, it checks the second shared column in both spreadsheets. It shouldn't check the entire column, only on rows where the first matches where identified
If it can find matches in the second lot of columns, in the row where these matches occurred, it should pull some data from the third column (on the first spreadsheet only!). This data should then be copied to the matched row (where there was a match from the first and second checks) on the second spread sheet!

I have tried to make this myself but didn't really get anywhere.
I think I need a for loop to check the first columns, then a second for loop to check the second set of columns (inside the first for loop), then a third for loop with if else statements inside to copy/not copy data on every match found in the second for loop.

I had made demo sheets which I wanted to use to help me explain, but I can't see how to upload them.

Is there base starting code about I could use, I'm not really familiar with vba...
I think I'd learn better by manipulating something that already exists than going from scratch.

Any help would be greatly appreciated!

 

[Fluff]

Hi & welcome to the board
This code needs to go in the source workbook

Sub Check2Columns()

    Dim Wbk As Workbook
    Dim Srcsht As Worksheet
    Dim DestSht As Worksheet
    Dim Dict As Object
    Dim Cl As Range
    Dim ValU As String
    
    Set Wbk = Workbooks.Open("[COLOR=#ff0000]C:\Users\Fluff\Documents\Excel files\Book11.xlsm[/COLOR]")
    Set DestSht = Wbk.Sheets("[COLOR=#ff0000]Input[/COLOR]")
    Set Srcsht = ThisWorkbook.Sheets("[COLOR=#ff0000]Test[/COLOR]")
    Set Dict = CreateObject("scripting.dictionary")
    
    With Srcsht
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp)) ' << uses col A as one of the columns to check
            ValU = Cl.Value & Cl.Offset(, 1).Value '<< concatenates cols A & B if you wanted to use A & C change the offset from 1 to 2
            If Not Dict.exists(ValU) Then Dict.Add ValU, Cl.Offset(, 3).Value '<< stores the value of Col D to output in destsht, change offset value to suit
        Next Cl
    End With
    With DestSht
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp)) ' << uses col A as one of the columns to check
            ValU = Cl.Value & Cl.Offset(, 1).Value '<< concatenates cols A & B if you wanted to use A & C change the offset from 1 to 2
            If Dict.exists(ValU) Then Cl.Offset(, 7).Value = Dict.Item(ValU) '<< copies the value from Col D srcsht to col H destsht change offset to suit
        Next Cl
    End With


End Sub

It will open the destination book, compare cols A & B in the 2 books & where they match copy col D from the source book to col H of the destination book.
Change the values in red to suit. I've commented the code to show what needs to be changed depending on which columns you want to compare.
Any problems let me know