Using "with" block to make selection on book1 still selects book2

[yousufj56]

Hi,

I'm trying to select a range from book1 but for some reason excel keeps selecting that range from book2. Can someone please have a look to see if there is something i'm getting wrong on this.

Here is the code:

Dim book1Name As String
book1Name = "fromemail.xlsx"


Dim book1NamePath As String
book1NamePath = "C:\Users\user\Desktop\changes\" & book1Name


Dim book2Name As String
book2Name = "2wchanges.xlsx"    'modify it as per your requirement


Dim book2NamePath As String
book2NamePath = "C:\Users\user\Desktop\changes\" & book2Name


If IsOpen(book1Name) = False Then Workbooks.Open (book1NamePath)
Set book1 = Workbooks(book1Name)


If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
Set book2 = Workbooks(book2Name)


With book1.Sheets(1)
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set a = Selection
    
    x = 1
    For Each b In a.Rows


    x = x + 1


    Set lookFor = book1.Sheets(1).Cells(x, 3)   ' value to find
    Set srchRange = book2.Sheets(1).Range("C:H")    'source


    lookFor.Offset(0, 2).Value = Application.VLookup(lookFor, srchRange, 6, False)


Next
End With

 

[JoeMo]

If book2 is not open when you run the macro, the macro will open it, making it the active workbook, at the point where your with block executes. Your with block fails to qualify the range (do this with a dot "." before the range like this: .Range("A2").Select) so the default range which is the specified range in the active workbook is used.

 

[Fluff]

Try

With book1.Sheets(1)
    Set a = .Range("A2", .Range("A2").End(xlDown))
    
    x = 1
    For Each b In a.Rows


      x = x + 1
   
   
       Set lookFor = .Cells(x, 3)   ' value to find
       Set srchRange = book2.Sheets(1).Range("C:H")    'source
   
   
       lookFor.Offset(0, 2).Value = Application.vlookup(lookFor, srchRange, 6, False)


   Next
End With

 

[yousufj56]

@Fluff @JoeMo

Thank you both for the explanation and the code. I tried the code it works.

Joe, I will keep that in mind in the future. Thanks for explaining.

 

[Fluff]

Glad we could help & thanks for the feedback