Select Case Construct Help

[YellowLabPro]

I have started this, but don't quite know how to continue. What I am looking to do is if in col. P the word "Snowboards" exists then replace the value in col. H. of 157cm w/ 155cm-157cm; 158cm replace w/ 158cm-160cm, etc....

[VBA]
Sub AttribSize()
Dim c As Range
Dim i As Long, LRow As Long
Dim oWs As Worksheet
Set oWs = Workbooks("Complete_Upload_File_Green.xls").Sheets("EC Products")
LRow = LR(oWs, 1)
For Each c In Range("P4:P" & LRow).Value
Select Case Cells(i, "P").Value
Case "Snowboards"
Cells(i, "H").Value = "155cm"



End Sub
[/VBA]

[Norie]

Doug

If it's only Snowboards you are looking for then you'd be better of with If rather than Select Case.

Though you would use a Select Case for the replacements.

[YellowLabPro]

Hello Norie,
It is going to be more Cases, (Skateboards and Wakboards) and a lot size subsitutes. I kept it simple for the post.

[Bob Phillips]

[vba]

Sub AttribSize()
Dim c As Range
Dim i As Long, LRow As Long
Dim oWs As Worksheet
Set oWs = Workbooks("Complete_Upload_File_Green.xls").Sheets("EC Products")
LRow = LR(oWs, 1)
For Each c In Range("P4:P" & LRow).Value
If Cells(i, "P").Value = "Snowboards" Then

Select Case Cells(i, "H").Value

Case "155cm": Cells(i, "H").Value = "155cm-157cm"

Case "158cm": Cells(i, "H").Value = "158cm-160cm"

'etc.

End Select
End If
Next c

End Sub
[/vba]

[Norie]

Doug

If you want help with code please post the actual code or at least something representative of it and/or an explanation of what the code is meant for.

If it is multiple values you are looking for in P then you probably should use Select Case.

And if it's multiple values you want to change you should probably use nested Select Cases.
[vba]
Select Case c.Value
Case "Snowboards"
Select Case Cells(i, "H").Value

Case "155cm": Cells(i, "H").Value = "155cm-157cm"

Case "158cm": Cells(i, "H").Value = "158cm-160cm"
End Select
Case "Skateboards"
Select Case Cells(i, "H").Value

Case "155cm": Cells(i, "H").Value = "155cm-157cm"

Case "158cm": Cells(i, "H").Value = "158cm-160cm"
End Select
' etc
End Select
[/vba]
By the way why aren't you using c in the code?

[Bob Phillips]

You can probably groupthe product case in some cases

[vba]


Select Case Cells(i, "P").Value

Case "Snowboards", "Skateboards"

Select Case Cells(i, "H").Value
[/vba]

[mdmackillop]

Two options. loop all cells or Find
[VBA]Option Compare Text

Sub AttribSize()
Dim c As Range
Dim i As Long, LRow As Long
Dim oWs As Worksheet, firstaddress As String
Dim Arr
Set oWs = ThisWorkbook.Sheets("EC Products")
LRow = 13025 'lr(oWs, 1)

With oWs.Range("P4:P" & LRow)
Set c = .Find("Snowboards", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Select Case Val(c.Offset(, -8))
Case Is = 0: c.Offset(, -7) = "No Size"
Case Is <= 140: c.Offset(, -7) = "100cm-140cm"
Case Is <= 150: c.Offset(, -7) = "140cm-150cm"
'etc.
End Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

End Sub

Sub AttribSize2()
Dim c As Range
Dim i As Long, LRow As Long
Dim oWs As Worksheet
Set oWs = ThisWorkbook.Sheets("EC Products")
LRow = 13025 'lr(oWs, 1)
For i = 4 To LRow
If Cells(i, "P") = "Snowboards" Then
Select Case Val(Cells(i, "H"))
Case Is = 0: Cells(i, "I") = "No size"
Case Is <= 140: Cells(i, "I") = "100cm-140cm"
Case Is <= 150: Cells(i, "I") = "140cm-150cm"
'etc.
End Select
End If
Next
End Sub
[/VBA]