Microsoft Office Forums - View Single Post - [Solved] Compare cells in 2 columns in separate WBs and return data in Col 38-40 if there is match

krispykreme · 09-05-2016, 03:01 PM

Hi

I am attempting to compare 2 columns in separate workbooks, and if a match is found, to return the data in columns 38-40 from one workbook into columns 38-40 of the second workbook. I thought the best way to do this was to progressively loop through the values of the column in WB1, and compare with the other WB using a vlookup function. Unfortunately macros are not my strongest suit and essentially I've been trying to draw upon my knowledge of other coding languages and translate it into macro. This probably isn't the best way. I'm currently going through tutorials in order to find a more efficient way to do this function, but in the meantime, here's what I have:

Code:

Sub DataTransfer()
Dim WBNEW As Workbook 'current week's workbook
Dim WBOLD As Workbook 'previous week's workbook
Dim WSNEW As Worksheet 'current week's worksheet
Dim WSOLD As Worksheet 'previous week's worksheet

'Open all relevant workbooks also make them macro enabled?
Set WBNEW = Workbooks("wk8.xlsm") 'change name of current week's workbook
Set WBOLD = Workbooks("wk7.xlsm") 'change name of previous week's workbook
Set WSNEW = WBNEW.Worksheets("Open cases") 'change if relevant worksheet has different name
Set WSOLD = WBOLD.Worksheets("Open cases") 'change if relevant worksheet has different name

Dim last As Double
With WSNEW
        last = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
'counts the last row

Dim inew As Range
Set inew = WSNEW.Range("A2:A" & last)
'sets the range to be searched in previous week's workbook (Col 1 all rows)

Dim i As Variant
Dim v1 As String
Dim v2 As String
Dim v3 As String

For Each i In inew
    v1 = Application.VLookup(i, WSOLD, 38, False)
    v2 = Application.VLookup(i, WSOLD, 39, False)
    v3 = Application.VLookup(i, WSOLD, 40, False)
    Next
    On Error Resume Next
End Sub

The workbooks will vary on a week by week basis, thus I thought to define them earlier to make it easier to adjust, and the range of active cells in the sheet will vary week by week too (not sure if I've properly defined the array). Code seems to be getting stuck once it hits the Vlookups so I must be doing something wrong there. This is probably a pretty terrible code but any advice would be appreciated.

Thanks!