VBA: Returning A Worksheets Object Reference From A Function

You are returning the wrong type of object in your function.

Function get_ExampleSheet() As Worksheets    get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")End Function

This currently has several errors.

Function get_ExampleSheet() As Worksheet   Set get_ExampleSheet = ActiveWorkbook.Sheets("Sheet1")End Function

Note I changed:

1. Return type to Worksheet (you are trying to set a variable, wSheet, which is of type Worksheet to a Worksheets type variable)
2. Added set keyword
3. Changed to .Worksheets to .Sheets to return the specific sheet you are interested in

Sub Lookup()Dim state As VariantDim PodName As VariantSet state = ThisWorkbook.Worksheets("Sheet1").Range("E:E")Sheets("Sheet1").ActivatePodName = WorksheetFunction.VLookup(state, Range("A1:C55"), 2, False)ThisWorkbook.Worksheets("Sheet1").Range("F:F") = PodNameEnd Sub

Macro should stop once the target cell is blank

In order to return an Object from a function, you need to provide the Set keyword:

Function get_ExampleSheet() As Worksheet    Set get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")End Function