0

what I am trying to do is fairly straight forward:

  1. Select any range of cell in WorkBook A (with value in it)
  2. Look up every single value in that selected range from an (two columns) array in WorkBook B (say A1:B10000)
  3. Return the value from the 2nd column of the array to Workbook B back to WorkBook A to the columns immediately to the right next to the range selected in step 1.

Here is the code I have been working so far.

Sub Checker() 
Dim rw As Long, x As Range 
Dim extwbk As Workbook, twb As Workbook 
Dim SelRange As Range

Set twb = ThisWorkbook
Set SelRange = Selection
Set extwbk = Workbooks.Open("path to the file in my harddrive")
Set x = extwbk.Worksheets("Sheet1").Range("A1:B100000")

With twb.ActiveSheet

    For rw = Selection.Row To Selection.Rows.Count + rw - 1
        .Cells(rw, Selection.Column + 1) = Application.VLookup(.Cells(rw, Selection.Column).Value2, x, 2, False)

    Next rw

End With

Somewhere in the section part of the code something is wrong but I cannot really figure it out. Could any of you folks help?

Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
jj_cale
  • 1
  • Inside the With/End With section, any reference that begins with a dot/period refers to twb.ActiveSheet. – Rich Holton May 05 '17 at 14:00
  • "Why is this not working" isn't really a question. Do you get an error message? If so what line? If not what outcome do you get that is wrong? – Tim Wilkinson May 05 '17 at 14:10
  • Also, while it's suggested to always [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251), I notice you use `Set SelRange = Selection`. Later in your code though you go back to using `Selection`. I suggest changing that to `SelRange.Row` and `SelRange.Column` instead. – BruceWayne May 05 '17 at 14:22

0 Answers0