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....


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

[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]

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

[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.

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

By the way why aren't you using c in the code?:dunno

[Bob Phillips]

You can probably groupthe product case in some cases




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

Case "Snowboards", "Skateboards"

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

[mdmackillop]

Two options. loop all cells or Find
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