1

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
shA.t
  • 16,580
  • 5
  • 54
  • 111
JonYork
  • 1,223
  • 8
  • 31
  • 52
  • Start by recording your actions (*hint* - use relative references). When you are done, tap Alt+F11 to enter the VBE and see what you've done and where it can be improved. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. –  May 19 '15 at 17:15
  • Now that, I was not aware of! – JonYork May 19 '15 at 17:22
  • If you run into trouble drop back and edit your question to include enough code that we can reproduce the difficulty and focused help should be forthcoming. –  May 19 '15 at 17:24
  • 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 Thanks – JonYork May 19 '15 at 19:11

1 Answers1

1

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.