.find issue

[depcdivr]

OK I am stumped here trying to figure out what I need to do. Here is the scenario

I have a userform with 4 comboboxes named

item
clip
sku
color


each item corresponds to a unique worksheet. When the item is selected I want to update the comboboxes for the clip (found in row 10), sku (row 11) and color (row 9). I can do this no problem.

When I change the sku, I want to find the apporpriate clip and color. Since it is unique it is not an issue. The clip is defined by the characters 5 and 6. the color is defined by digits 8 through 10.

The middle digits(5&6) of the sku are defined as follows

01 = Mini Alligator
02= Alligator
04 = 40mm
06 = 60mm
13 = Newborn
14 = Infant
15 = 3 Yrs +


when I change the clip I want to update the sku to reflect the new number. this is not an issue either.

The probelm comes about when I want to change the color. On certain pages I may have multiple items for a certain color.

For instance I may have an item that is a white but is available as a Newborn, Infant or 3 Yrs +. In other words, there are three different sku's for that color.

there would be sku's
033-13-001
033-14-001
033-15-001

I want to make sure that when I select the color it does the .find to get the correct sku number based on what clip style was selected. When I do the .find it finds the first occurance of the color and which is always the Newborn clip and the sku is not right.

Here is the code that I am using. I just can not seem to figure out how to check to make sure that when I find the color it checks the clip and if it does not match it will move search for the next occurance until it finds the combination where the color and clip match. this will give the unique sku for the product.

Private Sub UserForm_Initialize()
sheet_count = ThisWorkbook.Sheets.count
x = 1
For Each ws In Worksheets
    If ws.Name <> "Show" And ws.Name <> "ToDo" And ws.Name <> "Prices" And ws.Name <> "Inventory" And ws.Name <> "Main" And Left(ws.Name, 5) <> "-old-" Then
        With Me.item
            .AddItem ws.Name
        End With
        With Me.cc_item
            .AddItem ws.Name
        End With
    End If
Next ws
Me.rightside.Visible = False
Me.LeftSide.Visible = False
Me.sales.Value = 0
With Me.data
        .ColumnCount = 8
        .ColumnWidths = "125;60;55;35;250;35;50;50"
        .AddItem
        .List(.ListCount - 1, 0) = "Item"
        .List(.ListCount - 1, 1) = "SKU"
        .List(.ListCount - 1, 2) = "Clip"
        .List(.ListCount - 1, 3) = "Side"
        .List(.ListCount - 1, 4) = "Description"
        .List(.ListCount - 1, 5) = "Qty"
        .List(.ListCount - 1, 6) = "Unit Price"
        .List(.ListCount - 1, 7) = "Item Total"
 
        End With
Me.subprice.Value = Format(0#, "$0.00")
Me.Tax.Value = Format(0#, "$0.00")
Me.Total = Format(0#, "$0.00")
Price_total = 0
End Sub
 
 
Private Sub item_Change()
Call update_clip
Call update_color
Me.Qty.Value = 1
End Sub
 
 
Private Sub clips_Change()
Call update_color
'Call update_price
 
If Me.clips.Value = "Newborn" And Mid(Me.sku.Value, 5, 2) <> "13" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "13" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "Infant" And Mid(Me.sku.Value, 5, 2) <> "14" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "14" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "3 Yrs +" And Mid(Me.sku.Value, 5, 2) <> "15" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "15" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "Alligator" And Mid(Me.sku.Value, 5, 2) <> "02" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "02" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "Mini Alligator" And Mid(Me.sku.Value, 5, 2) <> "01" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "01" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "40mm" And Mid(Me.sku.Value, 5, 2) <> "04" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "04" & Mid(Me.sku.Value, 7)
End If
If Me.clips.Value = "60mm" And Mid(Me.sku.Value, 5, 2) <> "06" Then
    Me.sku.Value = Left(Me.sku.Value, 4) & "06" & Mid(Me.sku.Value, 7)
End If
 
End Sub
 
 
Private Sub color_Click()
Call update_price
Set ws = Worksheets(Me.item.Value)
icol = ws.Cells(9, Columns.count).End(xlToLeft).Offset(0, 1).Column
   Dim Found As Range
    Set Found = ws.Rows(9).Find(What:=Me.color.Value, LookAt:=xlWhole)
 
    If Not Found Is Nothing Then
        If Right(Me.sku.Value, 3) <> Right(Found.Offset(2), 3) Then
                If Me.clips.Value = "Alligator" Then
                    clipnum = "02"
                End If
                If Me.clips.Value = "Mini Alligator" Then
                    clipnum = "01"
                End If
                If Me.clips.Value = "40mm" Then
                    clipnum = "04"
                End If
                If Me.clips.Value = "60mm" Then
                    clipnum = "06"
                End If
                If Me.clips.Value = "Newborn" Then
                    clipnum = "13"
                End If
                If Me.clips.Value = "Infant" Then
                    clipnum = "14"
                End If
                If Me.clips.Value = "3 Yrs +" Then
                    clipnum = "15"
                End If
 
'        Me.sku.Value = Found.Offset(2)  'Offset two rows up
            Me.sku.Value = Left(Found.Offset(2), 4) & clipnum & Mid(Found.Offset(2), 7)
        End If
 
    Else
'        MsgBox "No match found."
        Me.sku.Value = ""
    End If
End Sub
 
 
Private Sub sku_change()
If Me.cc_item.Value = "select item" Then Exit Sub
 Set ws = Worksheets(Me.item.Value)
icol = ws.Cells(11, Columns.count).End(xlToLeft).Offset(0, 1).Column
   Dim Found As Range
    Set Found = ws.Rows(11).Find(What:=Me.sku.Value, LookAt:=xlWhole)
 
    If Not Found Is Nothing Then
        Me.color.Value = Found.Offset(-2)   'Offset two rows up
 
    Else
 '       MsgBox "No match found."
        Me.color.Value = ""
    End If
        If Right(Me.sku.Value, 1) = "L" And Me.LeftSide.Value <> True Then Me.LeftSide.Value = True
        If Right(Me.sku.Value, 1) = "R" And Me.rightside.Value <> True Then Me.rightside.Value = True
If Me.clips.Value <> "Alligator" And Mid(Me.sku.Value, 5, 2) = "02" Then
    Me.clips.Value = "Alligator"
    End If
If Me.clips.Value <> "Mini Alligator" And Mid(Me.sku.Value, 5, 2) = "01" Then
    Me.clips.Value = "Mini Alligator"
End If
If Me.clips.Value <> "40mm" And Mid(Me.sku.Value, 5, 2) = "04" Then
    Me.clips.Value = "40mm"
End If
If Me.clips.Value <> "60mm" And Mid(Me.sku.Value, 5, 2) = "06" Then
    Me.clips.Value = "60mm"
End If
If Me.clips.Value <> "Newborn" And Mid(Me.sku.Value, 5, 2) = "13" Then
    Me.clips.Value = "Newborn"
End If
If Me.clips.Value <> "Infant" And Mid(Me.sku.Value, 5, 2) = "14" Then
    Me.clips.Value = "Infant"
End If
If Me.clips.Value <> "3 Yrs +" And Mid(Me.sku.Value, 5, 2) = "15" Then
    Me.clips.Value = "3 Yrs +"
End If
End Sub
 
 
Private Sub update_color()
Set ws = Worksheets(Me.item.Value)
Dim color_type() As String
icol = 0
icol = ws.Cells(9, Columns.count).End(xlToLeft).Offset(0, 1).Column
If icol > 2 Then
    fcount = 0
 
    ReDim Preserve color_type(0 To 1)
    color_type(0) = ""
 
    For x = 2 To icol
        y = 0
 
        Do While y <= fcount
 
           If ws.Cells(9, x) <> color_type(y) And ws.Cells(9, x) <> "Description" Then
 
               If y = fcount Then
 
                   ReDim Preserve color_type(0 To fcount + 1)
 
                    color_type(fcount) = ws.Cells(9, x)
 
                    y = y + 2
 
                    fcount = fcount + 1
                End If
 
 
            Else: Exit Do
 
            End If
            y = y + 1
         Loop
    Next x
 
    Me.color.Clear
    For z = 0 To fcount
        With Me.color
                .AddItem color_type(z)
        End With
    Next z
        Me.color.Value = color_type(0)
End If
End Sub
 
 
Private Sub update_clip()
Set ws = Worksheets(Me.item.Value)
Dim clip_type() As String
Me.sku.Visible = True
Me.clips.Visible = True
Me.color.Visible = True
Me.Label5.Visible = True
icol = 0
icol = ws.Cells(10, Columns.count).End(xlToLeft).Offset(0, 1).Column
If icol > 2 Then
 
 
fcount = 0
 
ReDim Preserve clip_type(0 To 1)
clip_type(0) = ""
 
For x = 2 To icol
    y = 0
 
    Do While y <= fcount
 
       If ws.Cells(10, x) <> clip_type(y) And ws.Cells(10, x) <> "Clip Type" Then
 
           If y = fcount Then
 
               ReDim Preserve clip_type(0 To fcount + 1)
 
                clip_type(fcount) = ws.Cells(10, x)
 
                y = y + 2
 
                fcount = fcount + 1
            End If
 
        Else: Exit Do
 
 
        End If
        y = y + 1
     Loop
Next x
 
Me.clips.Clear
For z = 0 To fcount
    With Me.clips
            .AddItem clip_type(z)
    End With
Next z
    Me.clips.Value = clip_type(0)
End If
Select Case Me.item.Value
Case Is = "1.5in Traditional Bows"
Me.rightside.Visible = True
Me.LeftSide.Visible = True
 
Case Is = "2in Traditional Bows"
Me.rightside.Visible = True
Me.LeftSide.Visible = True
Case Is = "3in Traditional w knot"
Me.rightside.Visible = True
Me.LeftSide.Visible = True
 
Case Is = "4in Traditional w knot"
Me.rightside.Visible = True
Me.LeftSide.Visible = True
Case Is = "2-D Turtles"
Me.rightside.Visible = True
Me.LeftSide.Visible = True
Case Else
Me.rightside.Visible = False
Me.LeftSide.Visible = False
End Select
Me.sku.Clear
icol = ws.Cells(10, Columns.count).End(xlToLeft).Offset(0, 1).Column
Me.sku.Value = ws.Cells(11, 4)
For y = 4 To icol
    With Me.sku
        .AddItem ws.Cells(11, y)
    End With
Next y
End Sub
 
 
Private Sub LeftSide_Click()
    If Right(Me.sku.Value, 1) <> "L" Then Me.sku.Value = Left(Me.sku.Value, 10) & "L"
End Sub
 
Private Sub rightSide_Click()
    If Right(Me.sku.Value, 1) <> "R" Then Me.sku.Value = Left(Me.sku.Value, 10) & "R"
End Sub

Can anybody help review the code to point me in the right direction on what I need to fix? If you need more information I can email the full file and setup a conference call to go over what I am trying to do.