Public Variant problem

[kylekelsch]

Okay here is my problem. I have the below code in a module.
_______________________________________________

Option Explicit

Public rItem As Range
Public rItemInfo As Range

Public rOpt1 As Range
Public rOpt2 As Range
Public rOpt3 As Range
Public rOpt4 As Range
Public rOpt5 As Range

Public vOpt1Lookup As Variant
Public vOpt2Lookup As Variant
Public vOpt3Lookup As Variant
Public vOpt4Lookup As Variant
Public vOpt5Lookup As Variant

_____________________________________________________

Sub SetItemFieldVariables(MyCell As Range)

Set rItem = Cells(MyCell.Row, "G")
Set rItemsInfo = Worksheets("Items_PriceList").Range("ItemsInfo")

Set rOpt1 = Cells(MyCell.Row - 1, "U")
Set rOpt2 = Cells(MyCell.Row - 1, "Y")
Set rOpt3 = Cells(MyCell.Row - 1, "AC")
Set rOpt4 = Cells(MyCell.Row - 1, "AG")
Set rOpt5 = Cells(MyCell.Row - 1, "AK")

vOpt1Lookup = Application.VLookup(rItem, rItemsInfo, 35, False)
vOpt2Lookup = Application.VLookup(rItem, rItemsInfo, 40, False)
vOpt3Lookup = Application.VLookup(rItem, rItemsInfo, 45, False)
vOpt4Lookup = Application.VLookup(rItem, rItemsInfo, 50, False)
vOpt5Lookup = Application.VLookup(rItem, rItemsInfo, 55, False)
______________________________________________________

Sub ItemEntry(MyCell As Range)

If MyCell.Value <> "" Then
rOpt1.Value = vOpt1Lookup
rOpt2.Value = vOpt2Lookup
rOpt3.Value = vOpt3Lookup
rOpt4.Value = vOpt4Lookup
End If

End Sub
______________________________________________________

SetItemFieldVariables is triggered by the Worksheet_SelectionChange
event so when I select a cell it sets my variables. ItemEntry is
triggered by the Worksheet_Change event so when I change MyCell, rOpt1
thru rOpt5 should result with the lookup values. MyCell is the Target
pass in from the change events.

My result for rOpt1 thru rOpt5 is #N/A.

Now If I define my lookup variables in ItemEntry like so:

Option Explicit

Public rItem As Range
Public rItemInfo As Range

Public rOpt1 As Range
Public rOpt2 As Range
Public rOpt3 As Range
Public rOpt4 As Range
Public rOpt5 As Range

Public vOpt1Lookup As Variant
Public vOpt2Lookup As Variant
Public vOpt3Lookup As Variant
Public vOpt4Lookup As Variant
Public vOpt5Lookup As Variant
_____________________________________________________

Sub SetItemFieldVariables(MyCell As Range)

Set rItem = Cells(MyCell.Row, "G")
Set rItemsInfo = Worksheets("Items_PriceList").Range("ItemsInfo")

Set rOpt1 = Cells(MyCell.Row - 1, "U")
Set rOpt2 = Cells(MyCell.Row - 1, "Y")
Set rOpt3 = Cells(MyCell.Row - 1, "AC")
Set rOpt4 = Cells(MyCell.Row - 1, "AG")
Set rOpt5 = Cells(MyCell.Row - 1, "AK")
______________________________________________________

Sub ItemEntry(MyCell As Range)

vOpt1Lookup = Application.VLookup(rItem, rItemsInfo, 35, False)
vOpt2Lookup = Application.VLookup(rItem, rItemsInfo, 40, False)
vOpt3Lookup = Application.VLookup(rItem, rItemsInfo, 45, False)
vOpt4Lookup = Application.VLookup(rItem, rItemsInfo, 50, False)
vOpt5Lookup = Application.VLookup(rItem, rItemsInfo, 55, False)

If MyCell.Value <> "" Then
rOpt1.Value = vOpt1Lookup
rOpt2.Value = vOpt2Lookup
rOpt3.Value = vOpt3Lookup
rOpt4.Value = vOpt4Lookup
End If

End Sub
______________________________________________________

My result for rOpt1 thru rOpt5 are the correct values for the lookup.
So my question is, Does anyone know how I can get this to work by
defining my lookup variables in SetItemFieldVariables like I have it
shown the first way? That way I can use the lookup variables in other
macros by just calling the SetItemFieldVariables.