Trying to add vlookup to Excel

Question

I'm trying to create a formula that will check if a cell has a value in Column B and then input a vlookup in column A. As a standard formula it would look like

=VLOOKUP(B2,'Date Shown'!A:E,7,FALSE)

I would want the lookup value to change based on the cell thats originally being check. I have the below formula that can check and add a value to the adjacent cell, which i tried to modify for a vlookup, but don't have the knowledge to create a vlookup properly. I appreciate any help, thanks!

Option Explicit

Sub Macro1()

Dim r As Range
Dim LastRow As Long

With Sheets("Date Hidden")
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    For Each r In .Range("B2:B" & LastRow)
        If r.Value <> "" Then
            r.Offset(0, -1).result = Application.WorksheetFunction.VLookup(Sheets("Date Hidden").Range("A2"), Sheets("Date Shown")A:G, 7, False)
        End If
    Next r
End With

End Subs

Date Shown sheet:

Column A // Column G

Jane / / 10/1/17

Date Hidden Sheet

Column A //Column B
(empty) // Jane

Sheets("Date Shown")A:G should be Sheets("Date Shown").Range("A:G") — Scott Craner
Thanks for the quick response. I'm getting the following error "Unable to get vlookup value of the worksheet function class" Any ideas? — Eric L
@EricL Do you know if that value exists in the array? It seems like it can't find it. — Brownish Monster
On sheet "date Hidden" Column B has data i'm trying to vlookup, On sheet "Date Shown" Columns A:G have data, with Column A having the same identifier as the "date Hidden"s column B. So column B in the first sheet has a cells with values "3", "4", and "5" and those same values are also in some cells in the second sheets column A. — Eric L
What happens when you change r.Offset(0, -1).result = to r.Offset(0, -1).Value =? I don't believe result is a valid property. — TotsieMae

warner_sc warner_sc · Accepted Answer · 2018-01-25T21:36:56

Changed a little your code:

    Option Explicit

    Sub Macro1()

    Dim i As Long
    Dim date_hidden_range As Range
    Dim last_row As Long

    With Sheets("Date Hidden")
        last_row = .Cells(.Rows.Count, "B").End(xlUp).Row

        Set date_hidden_range = .Range("B2:B" & last_row)

        For i = 1 To date_hidden_range.Cells.Count
            If date_hidden_range(i).Value <> "" Then
                MsgBox date_hidden_range(i).Value
                date_hidden_range(i).Value = Application.WorksheetFunction.VLookup(Sheets("Date Hidden").Range("A" & i), Sheets("Date Shown").Range("A:G"), 7, False)
            End If
        Next i

    End With

    End Sub

Option 2

    Option Explicit

    Sub Macro1()

    Dim i As Long
    Dim cell_range As Range
    Dim date_hidden_range As Range
    Dim last_row As Long

    With Sheets("Date Hidden")
        last_row = .Cells(.Rows.Count, "B").End(xlUp).Row

        Set date_hidden_range = .Range("B2:B" & last_row)

        For Each cell_range In date_hidden_range
        i = 2
            If cell_range(i).Value <> "" Then
                MsgBox cell_range(i).Value
                cell_range(i).Value = Application.WorksheetFunction.VLookup(Sheets("Date Hidden").Range("A" & i), Sheets("Date Shown").Range("A:G"), 7, False)
            End If
        i = i + 1
        Next cell_range

    End With

    End Sub

Check how this work's, hope this helps you.

Trying to add vlookup to Excel

1 Answers