CODEWITHSUNDEEP
vbaexcel
JonYork
JonYork

Reputation: 1243

Find duplicate and copy adjacent cell into

I need some help getting some direction on this task.

I have two spreadsheets dumps with large quantities of information. I need to combine them into one organized sheet.

Spreadsheet A has path to the file (via hard drive), with loads of additional info needed to be retained. Spreadsheet B had path to the file (via hard drive), and for those in the content management system, the path in the CMS.

I would like to copy spreadsheet B into worksheet 2 in spreadsheet A, then run a macro that will search for matching values (path to file via hard drive), and where the same, copy the adjacent value of worksheet B (path in CMS) and copy that in the appropriate cell in spreadsheet A.

I have a vlookup function that does exactly what I need it to do, but how do I go about and put it in a macro to run?

=VLOOKUP(H1,A:B,2,FALSE)

How would I put this into a macro that returns the value, not just puts the formula in the cell?

Sub PCMSLookup()
    Dim LastRow As Long
    LastRow = Range("B" & Cells.Rows.Count).End(xlUp).Row
    Range("J15:J" & LastRow).Formula = "=VLOOKUP(B15,'PCMS-dump'!A:B,2,FALSE)" 
End Sub

Upvotes: 1

Views: 888

Answers (1)

user4039065
user4039065

Reputation:

The quickest way to put the value into the cells is to block in the formula then revert the formula to the returned value.

Sub PCMSLookup()
    Dim LastRow As Long
    with sheets("Sheet1")   '<-set this worksheet reference properly
        LastRow = .Range("B" & Cells.Rows.Count).End(xlUp).Row
        with .Range("J15:J" & LastRow)
          .Formula = "=VLOOKUP(B15, 'PCMS-dump'!A:B, 2, FALSE)" 
          .cells = .value2
        end with
    end with
End Sub

Note that when you are within a With/End With grouping, all of the range and cell references are prefixed with a period (e.g. . or full stop). This shows that they are children of closest With/End With.

Upvotes: 1

Related Questions