YellowLabPro
08-04-2007, 03:21 PM
I have two sheets that I run the same code on to edit a column's values. I am using the Select Case method.
It properly performs on the activesheet, it however fails on the the second sheet to perform the code. There is no error message(just for info). If I manually activate the second sheet and run the code it works then.
I have a reference to the second Sheet. The second sheet's name is PCCombined_VB. I use the With method.
My guess is either I am not properly or fully qualifying the reference to the second sheet. Can someone point out my oversight here please?
Sub StandSizes()
Dim shCalcSetting As Long
Dim c As Range
Dim mTimer As Single
Dim LRowf As Long, LRowV As Long
Dim Wsv As Worksheet
Set Wsv = Worksheets("PCCombined_VB")
' shCalcSetting = Application.Calculation
' Application.ScreenUpdating = False
' Application.Calculation = xlManual
LRowf = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("M4:M" & LRowf)
Select Case c.Value
Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"
Case "S", "Sm", "Small", "Sml"
c.Value = "Small"
Case "M", "Md", "Med", "Medium"
c.Value = "Medium"
Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"
Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"
Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"
Case "S/M"
c.Value = "Sm/Md"
Case "M/L"
c.Value = "Md/Lg"
Case "L/Xl"
c.Value = "Lg/Xl"
End Select
Next c
'----------------------------------------------------VB-------------------------------------------------
With Wsv
LRowV = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("M4:M" & LRowV)
Select Case c.Value
Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"
Case "S", "Sm", "Small", "Sml"
c.Value = "Small"
Case "M", "Md", "Med", "Medium"
c.Value = "Medium"
Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"
Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"
Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"
Case "S/M"
c.Value = "Sm/Md"
Case "M/L"
c.Value = "Md/Lg"
Case "L/Xl"
c.Value = "Lg/Xl"
End Select
Next c
End With
' ActiveSheet.Calculate
' Application.Calculation = shCalcSetting
' Application.ScreenUpdating = True
End Sub
It properly performs on the activesheet, it however fails on the the second sheet to perform the code. There is no error message(just for info). If I manually activate the second sheet and run the code it works then.
I have a reference to the second Sheet. The second sheet's name is PCCombined_VB. I use the With method.
My guess is either I am not properly or fully qualifying the reference to the second sheet. Can someone point out my oversight here please?
Sub StandSizes()
Dim shCalcSetting As Long
Dim c As Range
Dim mTimer As Single
Dim LRowf As Long, LRowV As Long
Dim Wsv As Worksheet
Set Wsv = Worksheets("PCCombined_VB")
' shCalcSetting = Application.Calculation
' Application.ScreenUpdating = False
' Application.Calculation = xlManual
LRowf = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("M4:M" & LRowf)
Select Case c.Value
Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"
Case "S", "Sm", "Small", "Sml"
c.Value = "Small"
Case "M", "Md", "Med", "Medium"
c.Value = "Medium"
Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"
Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"
Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"
Case "S/M"
c.Value = "Sm/Md"
Case "M/L"
c.Value = "Md/Lg"
Case "L/Xl"
c.Value = "Lg/Xl"
End Select
Next c
'----------------------------------------------------VB-------------------------------------------------
With Wsv
LRowV = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("M4:M" & LRowV)
Select Case c.Value
Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"
Case "S", "Sm", "Small", "Sml"
c.Value = "Small"
Case "M", "Md", "Med", "Medium"
c.Value = "Medium"
Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"
Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"
Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"
Case "S/M"
c.Value = "Sm/Md"
Case "M/L"
c.Value = "Md/Lg"
Case "L/Xl"
c.Value = "Lg/Xl"
End Select
Next c
End With
' ActiveSheet.Calculate
' Application.Calculation = shCalcSetting
' Application.ScreenUpdating = True
End Sub