需要 VBA 循环帮助

Question

我在工作簿中有 2 张纸 表 1 - 其中 A2 及更高版本有数字 名为"LOC"的工作表，我一次输入 1 个数字重新计算和格式化并保存它

该过程必须对工作表 1 - A2 及以下输入的所有数字重复，直到列末尾

请帮我循环这个

我必须从工作表 1 中复制每个数字并将其粘贴到 C2 中名为"LOC"的 heet 中，然后再次重复该过程

Sub MultipleSOA()
'1st SOA
Sheets("Sheet1").Select
Range("A2").Select
Selection.Copy
Sheets("Loc ").Select
Range("C2").Select
ActiveSheet.Paste
ActiveSheet.Calculate
Range("B9:G9").Select
Cells.Replace What:="PCL-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="SCL-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="PSI-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="CL-", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("B9:G9").Select
Range("C4").Select
Columns("C:C").ColumnWidth = 44.29
Range("C4").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UsersXXXXDesktopSOA" & ActiveSheet.Range("B9").Value & " - " & ActiveSheet.Range("C2").Value & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

Answer 1

我还没有测试过它，但请尝试以下代码...

Option Explicit
Sub MultipleSOA()
Dim varItemsToReplace As Variant
Dim varItem As Variant
Dim wksSource As Worksheet
Dim wksDest As Worksheet
Dim rngSource As Range
Dim rngCell As Range
varItemsToReplace = Array("PCL-", "SCL-", "PSI-", "CL-")
Set wksSource = Worksheets("Sheet1")
Set wksDest = Worksheets("Loc")
With wksSource
Set rngSource = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For Each rngCell In rngSource
With wksDest
.Range("C2").Value = rngCell.Value
.Calculate
For Each varItem In varItemsToReplace
.Range("B9:G9").Replace _
What:=varItem, _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next varItem
.Columns("C:C").ColumnWidth = 44.29
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:UsersXXXXDesktopSOA" & .Range("B9").Value & " - " & .Range("C2").Value & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End With
Next rngCell
End Sub