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.
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
I'm trying to design a Userform which returns Item Description and other details.
however i'm hitting errors all over the place.
Code:
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