0

The code below is taken from VBA vlookup reference in different sheet, but having small changes as I tried to adjusted to my own needs. Short, the vlookup code needs to looks in Sheet 1 - column A - starting from cell A4 until the last entry row, verifies if the existing data is in Sheet 2 - column G and if yes, take the value next to it from column I, go to Sheet 1 and paste that in column B right next to its specific value. However, my approach doesn't work. Any help will be must appreciated!

        Sub vlookupB()
            Dim vlookup As Variant
            Dim lastrow As Long
            Dim ws As Worksheet

        ' i tried to set this in order to take into consideration the last entry from column A.
            Set ws = Sheets("Sheet1")
            lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row 

            On Error Resume Next

            vlookup = Application.WorksheetFunction.vlookup(ws.Range("A4" & lastrow), _  'start from A4 until its last entry from the column
            Worksheets("Sheet2").Range("G7:I"), 3, False)

            On Error GoTo 0
            If IsEmpty(vlookup) Then

                ' do nothing    
            End If

            Range("B4:B") = vlookup 

        ' paste in column B from sheet1 starting from row B4 until the last entry I have in column A.

        ' so if for e.g. column A has entries until A200, then it should paste the value until B200 - of course, if the value is found in sheet2

    End Sub
alex2002
  • 161
  • 1
  • 11
  • `ws.Range("A4:A" & lastrow)`? – CMArg Apr 01 '18 at 13:41
  • tried that as well. No result.. – alex2002 Apr 01 '18 at 13:45
  • `Worksheets("Sheet2").Range("G7:I")` ? you need to have a row number for column `I` as well. Also `If IsEmpty(vlookup) Then` needs to be replaced with `If IsError(vlookup) Then` – Shai Rado Apr 01 '18 at 13:49
  • Thanks. Now works after i defined the range also for `Range("B4:B30000") = vlookup` but it has the following problem. If in **Sheet1** column A - i have entries only until A200, the vlookup will paste the data until B200, but B201 to B3000 will equal = #N/A because there are no values from A201. How can I fix this? Thanks – alex2002 Apr 01 '18 at 13:56
  • `Range("B4:B" & lastrow)`? – CMArg Apr 01 '18 at 14:00
  • My bad, works now. Thank you guys! – alex2002 Apr 01 '18 at 14:02

1 Answers1

1

Write the formulas into the cells then revert the formula results to their value.

Sub vlookupB()
    Dim vlookup As Variant
    Dim lastRow As Long, lastRow2 As Long
    Dim ws As Worksheet, ws2 As Worksheet

    Set ws = Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set ws2 = Sheets("Sheet2")
    lastRow2 = ws2.Cells(ws2.Rows.Count, "G").End(xlUp).Row

    With ws.Range("B4:B" & lastRow)
        .Formula = "=iferror(vlookup(A4, " & ws2.Range("G7:I" & lastRow2).Address(1, 1, external:=True) & ", 3, false), text(,))"
        .Value = .Value
    End With

End Sub