Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Variant problem
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variant valiables problem | Excel Discussion (Misc queries) | |||
Public variable problem | Excel Programming | |||
Variant array is empty problem | Excel Programming | |||
Variant userform problem | Excel Programming | |||
VBA problem pulling data out of variant/range input variables | Excel Programming |