VBA to lookup and show values

[bademployee]

Hi all,

I have a VBA problem & no idea how to solve.


Sheet 1: Depending on what is selected in the drop-down I'm needing values to show in B4:B11 (Max number of ingredients is 63, but each product has no more than 7 ingredients) & also, values to show in C4:C11.

Note - B4:B11 are also validation lists so needs to be VBA.

[/URL][/IMG]



Sheet 2: Houses the data for the drop-down & composition for each product.

[/URL][/IMG]


Thanks for any help in advance.

Mark

 

[bademployee]

Appreciate any help

 

[bademployee]

Found some VBA off a similar workbook as a starting point:

Private Sub ComboBox1_Click()
  If SkipComboChange = True Then
    Else
Dim Val As Integer
    On Error GoTo err_change


    NumProducts = 32


    If ActiveSheet.Name <> "Blend 1 (%)" Then
        Exit Sub
    End If


    Val = ComboBox1.Value


    If Val = 0 Then
        For i = 1 To NumProducts
            Cells(20 + i, 3).Value = 0
        Next
        End
    End If
    
    If CInt(Sheets("Standard Blends").Cells(3 + Val, NumProducts + 2).Value) <> 100 Then
        End ' Chose a heading, not a blend
    End If
    
    CurrentPercent = Cells(NumProducts + 22, 3).Value
    If CurrentPercent < 0 Then
        CurrentPercent = 0
    ElseIf CurrentPercent > 1 Then
        CurrentPercent = 1
    End If
    For i = 1 To NumProducts
        a = Sheets("Standard Blends").Cells(3 + Val, 1 + i).Value
        If a <> "" Then
            Cells(20 + i, 3).Value = Cells(20 + i, 3).Value + (a / 100) * (1 - CurrentPercent)
        Else
            'Cells(20 + i, 3).Value = 0
        End If
    Next
    End If
err_change:
    Exit Sub
End Sub

 

[spanksy]

In a simpler version, you could perhaps use a validation list for the product selection, which drives a filter on the second sheet to only show ingredients relating to that Product.

Have an On Selection macro which runs and copies the 63 ingredient columns & percentages (including blanks), transpose into Sheet1 and remove blanks (therefore ending up with 7 or less listed ingredients)?

I'm unclear on how the validation rules on B4:B11 apply - could you elaborate?

 

[bademployee]

Thanks spanksy,

Now have a validation list for product selection & transposed ingredients & blend % in sheet 2:

[/URL][/IMG]

I jumped ahead instead of filtering ingredients relating to the product, which gets me to asking - how can I filter the ingredients with a % value in B OR what VBA is required to copy the ingredients with a % over to B4:B11 on sheet 1?

B4:B11 are validation lists also - the user needs the functionality of selecting single ingredients OR in the case of a value in sheet1 B2 - the ingredients to show in B4:B11.

Thanks

 

[spanksy]

Re removing rows with 0 in them https://www.mrexcel.com/forum/excel-questions/85912-delete-row-if-cell-has-0-value.html

If you break the steps you want the user to work through into individual macros, then string together once you're happy that's probably easiest.

For example:
1) Select product
2) Return list of all ingredients & values (macro)
3) Remove 0 values (macro)
4) Re-apply validation rules on B4:B11 for user check

Cheers from NZ