选择案例陈述 [英] Select Case Statement
问题描述
你好，
我的选择案例陈述有点麻烦。 excel vba我很新。我的最终目标是重命名 细胞价值。例如，我正在尝试改变"FP + CCV"。进入第一方全部。我发布了我的代码示例。
请原谅我的代码很长。我正在使用记录宏。 提前谢谢
Sub RealTimeHistorical（）
'
'RealTimeHistorical Macro
'
'
选择案例单元格（i，7）.Value
案例"FP + CCV"
Combo =" FPCC"
案例"PCCC"
Combo ="PCCC"
案例"HB / NARS"
Combo ="HBCC"
案例"OEF / OIF"
Combo =" CVCC"
结束选择
表格（"Pipkins_schedhours"）。选择
范围（"D2：G1389"）。选择
Selection.Copy
表格（"实时历史记录"）。选择
范围（"B2"）。选择
Application.CutCopyMode = False
Selection.Cut Destination：= Range（" B3"）
范围（"B2：E2"）。选择
表格（"Pipkins_schedhours"）。选择
表格（"Pipkins_schedhours"）。姓名=" Pipkins_schedhours"
范围（"D2：G1389"）。选择
Selection.Copy
表格（"实时历史记录"）。选择
范围（"B2：E2"）。选择
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Clear
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"E3：E1389"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"C3：C1389"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
使用ActiveWorkbook.Worksheets（"实时历史记录"）。排序
.SetRange范围（" B2：E1389"）
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
。应用
结束与$
范围（"B2：E1389"）。选择
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Clear
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"E3：E1389"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"C3：C1389"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
列（"B：E"）。选择
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Clear
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"E2：E50000"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
ActiveWorkbook.Worksheets（"Real Time Historical"）。Sort.SortFields.Add Key：= _
范围（"C2：C50000"），SortOn：= xlSortOnValues，订单：= xlAscending，DataOption _
$
：= xlSortNormal
使用ActiveWorkbook.Worksheets（"实时历史记录"）。排序
.SetRange范围（"B1：E50000"）
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
。应用
结束与$
范围（"F2"）。选择
ActiveCell.FormulaR1C1 =" = WEEKNUM（RC [-3]）" b
范围（"F2"）。选择
Selection.AutoFill Destination：= Range（" F2：F1389"），Type：= xlFillDefault
范围（"F2：F1389"）。选择
ActiveWindow.LargeScroll Down：= - 2
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 584
ActiveWindow.ScrollRow = 390
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 1
下一步
结束次级
好的，我想通了。非常感谢！
Sub Pipkins_schedhours（）
'
' RealTimeHistorical Macro
'
'
Sheets（" Pipkins_schedhours"）。选择
NumRows = ActiveSheet.UsedRange.Rows.Count
For i = 2 To NumRows
Select Case Cells（i，7）.Value
Case" FP + CCV" b $ b 单元格（i，7）="First First All"
案例"PCCC"
单元格（i，7）="PCC All"
案例"HB / NARS"
单元格（i，7）="健康福利/ NARS"
案例"OEF / OIF"
单元格（i，7）=" CVCC Inbound"
结束选择
下一步
结束子
Hello,
I am having a little bit of trouble with my select case statement. I am very new when it comes to excel vba. My end state goal is to rename a cell value. For example I am trying ti change "FP + CCV" into First Party All. I posted a sample of my code. Pleae forgive me for my code is long. I was utilizing record macro. Thank you in advance
Sub RealTimeHistorical()
'
' RealTimeHistorical Macro
'
'
Select Case Cells(i, 7).Value
Case "FP + CCV"
Combo = "FPCC"
Case "PCCC"
Combo = "PCCC"
Case "HB / NARS"
Combo = "HBCC"
Case "OEF/OIF"
Combo = "CVCC"
End Select
Sheets("Pipkins_schedhours").Select
Range("D2:G1389").Select
Selection.Copy
Sheets("Real Time Historical ").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Cut Destination:=Range("B3")
Range("B2:E2").Select
Sheets("Pipkins_schedhours").Select
Sheets("Pipkins_schedhours").Name = "Pipkins_schedhours"
Range("D2:G1389").Select
Selection.Copy
Sheets("Real Time Historical ").Select
Range("B2:E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("E3:E1389"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("C3:C1389"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Real Time Historical ").Sort
.SetRange Range("B2:E1389")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B2:E1389").Select
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("E3:E1389"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("C3:C1389"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
Columns("B:E").Select
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("E2:E50000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Real Time Historical ").Sort.SortFields.Add Key:= _
Range("C2:C50000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Real Time Historical ").Sort
.SetRange Range("B1:E50000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("F2").Select
ActiveCell.FormulaR1C1 = "=WEEKNUM(RC[-3])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1389"), Type:=xlFillDefault
Range("F2:F1389").Select
ActiveWindow.LargeScroll Down:=-2
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 584
ActiveWindow.ScrollRow = 390
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 1
Next
End Sub
Okay I figured it out. Thanks very much!
Sub Pipkins_schedhours()
'
' RealTimeHistorical Macro
''
Sheets("Pipkins_schedhours").Select
NumRows = ActiveSheet.UsedRange.Rows.Count
For i = 2 To NumRows
Select Case Cells(i, 7).Value
Case "FP + CCV"
Cells(i, 7) = "First Party All"
Case "PCCC"
Cells(i, 7) = "PCC All"
Case "HB/NARS"
Cells(i, 7) = "Health Benefits / NARS"
Case "OEF/OIF"
Cells(i, 7) = "CVCC Inbound"
End SelectNext
End Sub
这篇关于选择案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
