所以不让我发布我的问题的图像,请参阅这里:https://i.stack.imgur.com/Stpir.png
这张照片显示了我正在努力做的事情。我需要一个VBA脚本,在H栏中查找"服务台",并将其交换为l列中的值因此,在图中,H列中的"Service Desk"将替换为l列中的"GDC - US Oracle DBA"。
这是一个巨大的(对我来说)脚本项目的最后一部分,我只是想不明白:(.
谢谢。
我的当前代码:
Sub sla_breach_formatter()
' Reformat the priority values to SLA Tracker Format.
Columns("F").Replace What:="1", Replacement:="1 - Critical", SearchOrder:=xlByColumns
Columns("F").Replace What:="2", Replacement:="2 - Business Impact", SearchOrder:=xlByColumns
Columns("F").Replace What:="3", Replacement:="3 - Standard", SearchOrder:=xlByColumns
Columns("F").Replace What:="4", Replacement:="4 - Non-Urgent", SearchOrder:=xlByColumns
' Reformat the task types to SLA Tracker Format.
Columns("K").Replace What:="Incident", Replacement:="INC", SearchOrder:=xlByColumns
Columns("K").Replace What:="Problem", Replacement:="PRB", SearchOrder:=xlByColumns
Columns("K").Replace What:="Service Request", Replacement:="SREQ", SearchOrder:=xlByColumns
' Reformat the Breach Type
Columns("I").Replace What:="*RESO*", Replacement:="Resolution", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I").Replace What:="*-RESP-FR-*", Replacement:="First Response", LookAt:=xlPart _
, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I").Replace What:="*-RESP-*", Replacement:="Response", LookAt:=xlPart _
, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I").Replace What:="*PROB*", Replacement:="Problem", LookAt:=xlPart _
, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Swap out "Service Desk" Assignment Group with Last Assignment Group
Dim cl As Range
For Each cl In Range("$H$2:$H" & Range("$H65536").End(xlUp).Row)
If UCase(cl) = "Service Desk" Then cl = Cells(cl.Row, 4)
Next cl
' Format the dates to Euro standard
Columns("A:B").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
' expand all the columns
Cells.Columns.AutoFit
' Reset focus to A1
Cells(1, 1).Select
End Sub
将您的For Each
替换为以下内容。您正在引用单元格(第4行)中的单元格,这是d列。您需要使用Offset方法。此外,UCase会将值大写,因此您需要对照SERVICE DESK进行检查。
Dim cl As Range
For Each cl In Range("$H$2:$H" & Range("$H65536").End(xlUp).Row)
If UCase(cl) = "SERVICE DESK" Then cl = cl.Offset(, 4)
Next cl