Object Required Error when Setting Variable

[ExcelKnut]

Hi All,

I'm getting an "Object required" compile error for the code below. I'm confident it's something simple but I can't figure it out. Please help me understand why.

Sub FindFrequency()

Dim FunctionSelected As String
Dim Frequency As String
Dim TeamFunctions As Worksheet

Set FunctionSelected = "Appeals"

'Frequency is either Daily, Weekly or Monthly based on the Function selected
Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)

MsgBox "The Frequency is : " & Frequency
End Sub

 

[AlphaFrog]

Remove the Set command.

Set FunctionSelected = "Appeals"

 

[bbott]

You don't need to set string variables. Just remove "Set".

 

[ExcelKnut]

When I remove "set" I get an "Object variable or With block variable not set" error and it stops at the code indicated below???

Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)

 

[Zack Barresse]

You'll get a run-time error if a worksheetfunction returns an error. By using False as the last argument you're stating an exact match, in which case there isn't one. When using this methodology in VBA I'd recommend error handling. Something like this...

On Error Resume Next
Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)
On Error Goto 0

This is a simplistic way of handling the error. The On Error Resume Next is dangerous, but let's code execute - even on error - and continue without raising an exception (e.g. error). You should follow it up with the On Error Goto 0 statement to resume error handling/exceptions. Otherwise you'd have the code running past every error and you'd never know.

On a more poignant note, you don't actually set the worksheet you declared, which means even if there was something to find, you'd still receive an error because you haven't defined 'TeamFunctions'. That would look something like this...

Set TeamFunctions = ThisWorkbook.Worksheets("Your sheet name goes here")

 

[mikerickson]

One quirk is that (if there is no match) Application.Worksheetfunction.VLookUp(...) will give a run-time error, but Application.VLookUp(...) will return an error value (CVErr(xlErrNA)), so another error checking approach would be

Frequency = Application.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)

If IsError(Frequency) Then
    MsgBox "match not found"
Else
    MsgBox "The Frequency is : " & Frequency
End If

 

[ExcelKnut]

There still seems to be a problem with the "...WorksheetFunction.VLookup..." line of code (see entire line below) since there's a cell in Column C that contains the single word "Appeals" and a Frequency is indicated in Column D of the same row???

FunctionSelected = "Appeals"

Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)

 

[Zack Barresse]

Again, your "TeamFunctions" worksheet object is not being set.

 

[ExcelKnut]

It appears you can't use the actual sheet name within the VLOOKUP formula. When I changed it from "TeamFunctions" (the sheet name) to "Sheet1" it worked perfectly. Please explain if you understand why it works now..because I don't.

This worked.
Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, Sheet1.Range("C1:G999"), 2, False)

This DID NOT worked.
Frequency = Application.WorksheetFunction.VLookup(FunctionSelected, TeamFunctions.Range("C1:G999"), 2, False)

 

[ExcelKnut]

It's working now. As Zack reminded me, I simply had to set the worksheet name ANDdo it correctly. Initially, I used Set ws = "TeamFunctions" which did not work. When I changed it to set ws = Worksheets("TeamFunctions"), it worked great.

Thank you all so much!!! I really appreciate your time and assistance.