Updating User Form

[bromy2004]

Hi everyone,

I'm trying to design a Userform which returns Item Description and other details.
however i'm hitting errors all over the place.

Private Sub Cross_Ref_Change()


Dim strCross_Ref As Variant
Dim strItem As Variant
Dim strVariant As Variant
Dim strDescription As Variant
Dim strUOM As Variant
Dim strItem_Category As Variant
Dim strItem_Category_Text As Variant
Dim strProduct_Group As Variant
Dim strProduct_Group_Text As Variant

'Set Default Values to Form
strCross_Ref = UserForm2.Cross_Ref
strItem = UserForm2.Item_No
strVariant = UserForm2.Variant_Code
strDescription = UserForm2.Description
strUOM = UserForm2.UOM
strItem_Category = UserForm2.Item_Category
strItem_Category_Text = UserForm2.Item_Category_Text
strProduct_Group = UserForm2.Product_Group
strProduct_Group_Text = UserForm2.Product_Group_Text

'Lookup Item No. and Variant Code
If Sheets("Cross Reference").Range("Cross_Ref_No").Find(What:=strCross_Ref, _
    LookIn:=xlValues, LookAt:=xlWhole) = "" Then
    strItem = ""
    Else
    strItem = Sheets("Cross Reference").Range("Cross_Ref_No").Find(What:=strCross_Ref, _
        LookIn:=xlValues, LookAt:=xlWhole).Offset(Columnoffset:=1)
End If


strVariant = Range("Cross_Ref_No").Find(What:=strCross_Ref, LookIn:=xlValues, _
    LookAt:=xlWhole).Offset(Columnoffset:=2)
strUOM = Range("Cross_Ref_No").Find(What:=strCross_Ref, LookIn:=xlValues, _
    LookAt:=xlWhole).Offset(Columnoffset:=3)


'Declare Ranges
Dim Item_Range As Variant
Dim Variant_Range As Variant
Dim Description_Range As Variant
Dim UOM_Range As Variant
Dim Category_Range As Variant
Dim Group_Range As Variant
'Specify Ranges
Item_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Item_No")
Variant_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Variant_Code")
Description_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Description")
UOM_Range = Application.Sheets("Stockkeeping Unit").Range("STK_UOM")
Category_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Category")
Group_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Group")


'Double Lookup
'Return Range
    Dim RetRng As Range

'working Ranges
'Item
    Dim Val1Rng As Range
'Variant
    Dim Val2Rng As Variant
'Return Row
    Dim Val1Row As Long
'final value
    Dim Final_Value As Variant


    'If No Values
    Final_Value = "No Matches"
    With Variant_Range
        Set Val2Rng = .Find(What:=strVariant, After:=.Cells(.Rows.Count, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False)
        If Not Val2Rng Is Nothing Then
            With Item_Range
                Set Val1Rng = .Find(What:=strItem, After:=.Cells(.Rows.Count, _
                    1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                Val1Row = Item_Range.Row
                Final_Value = Cells(Val1Row, RetRng.Column).Value
            End With
        End If
    End With
End Sub

the User form has 9 Input Boxes which will be auto-populated at the user types.

Sheets:
Cross Reference:
4 Columns:
Cross-Reference No.
Item No.
Variant Code
Unit Of Measure.


Stockkeeping Unit:
6 Columns
Item No.
Variant Code
Description
UOM
Item Category
Product Group

Codes: this sheet has the Item Category and Product Group Codes and the associated description
Code
Description


Users will type any Code into the Field 1.
The next 2 fields will update and populate the Item No. and Variant Code from the Cross-Reference Sheet.

another 4 Fields update to show the Description, UOM, Item Category and Product Group.

The last 2 fields populate the description from the last sheet.


Problem 1:
if there isn't a match with the Cross-Reference i get an error.
ive tried using Iferrror(check) and (if check =Nothing) and (if check ="")
but none of those catch the error properly.
i dont want to use On Error Resume Next because i want the "Description" Field on the Userform to show "No Matches"

Problem 2:
when there is a successful match it bombs out on,
"Item_Range = Application.Sheets("Stockkeeping Unit").Range("STK_Item_No")"
which i originally declared as Range

Problem 3:
When i changed it to Declare as Variant
i crash on the Double Lookup.
i got that from my other post
http://www.mrexcel.com/forum/showthread.php?t=403226

I know this is a tough problem but can anyone help me please?

Thanks in advance

-Bromy