VBA: Object Required - Simple Vlookup Code

[Seba Robles]

Hello,
I'm trying to set a variable in my code with a vlookup within a sheet. I get a runtime error 424 "object required" when it gets to the last line in the code below:

Dim wsFunc As WorksheetFunction
Set wsFunc = Application.WorksheetFunction

Dim ws As Worksheet
Set ws = Sheets("Parameters")

Dim rngLook As Range
Set rngLook = ws.Range("C:E")

Dim GreetName As Range
Dim MailTo As String

    MailTo = Sheets("Email").Cells(6,8)
    Set GreetName = wsFunc.VLookup(MailTo, rngLook, 3, False)

Any help is greatly appreciated!

 

[maabadi]

1. You Declare Mailto as String But give it Cell Address Not Value. Change it:

MailTo = Sheets("Email").Cells(6, 8).Value

2. You Declare GreetName as Range, But I think Vlookup Result Not Range. Are You Sure this is Correct Method?

 

[Seba Robles]

Awesome, thank you maabadi!

I added the .value to my MailTo variable and changed the GreetName from Range to Variant, it is working now :D

Here's the code for future reference

Dim wsFunc As WorksheetFunction
Set wsFunc = Application.WorksheetFunction

Dim ws As Worksheet
Set ws = Sheets("Parameters")

Dim rngLook As Range
Set rngLook = ws.Range("C:E")

Dim GreetName As Variant
Dim MailTo As String

    MailTo = Sheets("Email").Cells(6,8)
    GreetName = wsFunc.VLookup(MailTo, rngLook, 3, False)

 

[maabadi]

if you want Set Range for GreetName Not Value use this:

Dim wsFunc As WorksheetFunction
Set wsFunc = Application.WorksheetFunction

Dim ws As Worksheet
Set ws = Sheets("Parameters")

Dim Lr As Long
Lr = ws.Range("C" & Rows.Count).End(xlUp).Row

Dim GreetName As Range
Dim MailTo As String
Dim M As Long
    MailTo = Sheets("Email").Cells(6, 8).Value
    M = wsFunc.Match(MailTo, ws.Range("C1:C" & Lr), 0)
   Set GreetName = ws.Cells(M, 5)

And if want Value:

Dim wsFunc As WorksheetFunction
Set wsFunc = Application.WorksheetFunction

Dim ws As Worksheet
Set ws = Sheets("Parameters")

Dim rngLook As Range
Set rngLook = ws.Range("C:E")

Dim GreetName As String
Dim MailTo As String

    MailTo = Sheets("Email").Cells(6, 8).Value
   GreetName = wsFunc.VLookup(MailTo, rngLook, 3, False)