PDA

View Full Version : Solved: Select Case Construct Help



YellowLabPro
09-04-2007, 01:54 PM
I have several different Cases I need to build w/ the same instructions below. The values will be copied to different sheets. The Sheets to where the values will be copied will be the same sheet names; SNBD, SNBB, SNBT, SNBO. My first assessment is there will be 3 more Cases added to the current one. These are listed as a comment in the bottom of the code.
I recoginize I will need to chang my reference sheet in the Set Command... but have left it in tact for example purposes.
As you will see on the attachment, Bindings are on the SNBD sheet, and will go to SNBB. SNBB will have a different Array List for their header..., as will the other Cases.



oWst.Range("A1:F1").Value = Array("Product ID#", "Product Description", "Dept/Cat", "Filter by Style", "Filter by Style", "Filter by Level")

What I am asking for assistance w/ is how to begin setting this up. I have a slight idea and have been playing around w/ simple Select Case examples today- some pointers to get started would be appreciated.


Sub AttributesMal()
Dim oWss As Worksheet, oWst As Worksheet, oAws As Worksheet
Dim i As Long, ii As Long, lLrws As Long, lLrwt As Long
Set oWss = ActiveSheet
Set oWst = Workbooks("TGSProductsAttrib.xls").Worksheets("SNBD")
Set oAws = ActiveSheet

'MsgBox LRs(oAws, "BC", 3)
'Select Case SNOWBOARDS (Sheet SNBD)
oWst.Range("A1:F1").Value = Array("Product ID#", "Product Description", "Dept/Cat", "Filter by Style", "Filter by Shape", "Filter by Level")
ii = oWst.Cells(Rows.Count, "A").End(xlUp).Row ' + 1
With oAws
For i = 6 To LRs(oAws, "BC", 3)
If Application.CountA(.Cells(i, "BC").Resize(1, 3)) > 0 Then
ii = ii + 1
'Product Id#
oWst.Cells(ii, "A").Value = .Cells(i, "W").Value
'Product Description
oWst.Cells(ii, "B").Value = .Cells(i, "Y").Value
'Dept/Cat
oWst.Cells(ii, "C").Value = .Cells(i, "AD").Value & " " & .Cells(i, "AE").Value
'Style
oWst.Cells(ii, "D").Value = Cells(i, "BC").Value
'Shape
oWst.Cells(ii, "E").Value = Cells(i, "BD").Value
'Level
oWst.Cells(ii, "F").Value = Cells(i, "BE").Value
End If
Next i
End With
oWst.Columns("A:J").AutoFit
'Select Case SNOWBOARD BINDINGS (Sheet SNBB)
'Select Case SNOWBOARD BOOTS (Sheet SNBT)
'Select Case SNOWBOARD OUTERWEAR (Sheet SNBO)

'Select Case
'Vlookup
'Loop through all sheets in workbook
'Add semicolon between filters
'Filter by Price

MsgBox "Done", , "Status"
End Sub