CODEWITHSUNDEEP
excelvba
Mirko Stanisic
Mirko Stanisic

Reputation: 57

Lookup values from another sheet and paste results as values

I belive this is simple coding, but in unable to deal with that myself. I tried with macro recorder but i can t get desired result... Im not good at VBA coding :/ I have two worksheets, Sheet1 and Sheet2. In ws1 i have columns A,B,C where i enter data starting from row 10 and down. My source data is in ws2 in range A:J. I would need a macro to search values from ws1 Range B10: x (lastrow) in ws2 in column B and for searched value to return value from column A in ws2 to column A in ws1, and column G from ws2 to column C in ws1. A standard VlookUp/Xlookup function can do that job, but i need to have values in columns A,B,C in ws1 as values because my other code can t work if there is a function. Values in ws1 column B are values generated on condtion from column B in ws2. So row number is from row 10 to X.

Thanks in advance!

Functions i tried with, but that cause my other code to stop working.

Cell A10 function: =XLOOKUP(Sheet1!B10;Sheet2!B:B;Sheet2!A:A)

Cell C10 function: =XLOOKUP(Sheet1!B10;RSheet2!B:B;Sheet2!G:G)

Upvotes: 0

Views: 529

Answers (1)

CDP1802
CDP1802

Reputation: 16174

Option Explicit
Sub mylookup()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell As Range, iLastRow As Long
    Dim r, ar
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

    ' sheet2
    iLastRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row
    ar = ws2.Range("B1:B" & iLastRow)

    ' sheet1
    iLastRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    For Each cell In ws1.Range("B10:B" & iLastRow)
        r = Application.Match(cell, ar, 0)
        If Not IsError(r) Then
            cell.Offset(0, -1) = ws2.Cells(r, "A")
            cell.Offset(0, 1) = ws2.Cells(r, "G")
        End If
    Next
End Sub

Upvotes: 1

Related Questions