Search code examples
excelvbaloopsworksheet-functioncross-reference

Copying data against a key column from other worksheet. (Trying to do as vlookup is not working in VBA)

I want the code to use key data available to me and cross reference in next sheet to get the data from next column against the same key in that sheet. I have tried to use VLOOKUP but while it works fine manually it does not work while using in macro. Please refer to the code below:

Hence to get the results I have tried to search the data by using the loops against the column of data available to me. 

    Sheets("Details").Select
    Dim myval, strr As String
    Dim cell As Range
    Dim i, j, k As Integer
    j = 3
    i = ActiveWorkbook.Worksheets("Details").Range("A1", 
    Worksheets("Details").Range("A1").End(xlDown)).Rows.Count

    Sheets("Pd Details").Select
    Do While Cells(j, 1).Value <> ""
        myval = Cells(j, 1).Value
            For k = 3 To i
                Sheets("Details").Select
                If myval = Cells(k, 1).Value Then
                    strr = Cells(k, 2).Value
                    Sheets("Pd Details").Select
                    Cells(j, 4).Value = strr
                    Exit For
                End If
            Next
        Sheets("Pd Details").Select
        j = j + 1
    Loop

I want the output as search of the key and the result printed against the key in the 4th column from the data sheet which has the dump of data.

     Details sheet:

enter image description here Pd Details sheet: enter image description here enter image description here Resulting Pd Details sheet:

Solution

  • How about using VLookUp in VBA as below, this will get the last row in your PD Details worksheet and then use the Vlookup to get the desired results, then remove the formula so it leaves the values only.

    (I've made sure to remove any Select statement as these will only slow your code down)

    Sub Test()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsDetails As Worksheet: Set wsDetails = wb.Worksheets("Details")
Dim wsPDDetails As Worksheet: Set wsPDDetails = wb.Worksheets("Pd Details")
'declare and set the worksheets
Dim LastRow As Long

LastRow = wsPDDetails.Cells(wsPDDetails.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A on the PD Details sheet

wsPDDetails.Range("D1:D" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-3],Details!C[-3]:C,2,FALSE)"
'use the Vlookup formula

wsPDDetails.Range("D1:D" & LastRow).Value = wsPDDetails.Range("D1:D" & LastRow).Value
'convert the results from the formula to values and remove the formula itself.
End Sub