vba - 每次复制一个模板并使用 Excel 宏替换

Question

我的工作表 1 中有以下数据集

这个模板位于sheet 3

我愿意

1. 将模板从 sheet 3 复制到 sheet2
2. 读取 sheet1 第 1 行中的城市和名称
3. 替换工作表 2 中的城市和名称
4. 对工作表 1 中的每一行重复此操作

所以我理想的输出是:

正如您所看到的，因为工作表 1 中有三行，所以模板被复制了三次。

我尝试了这段代码，但它不会循环“sheet1”

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim ii As Long

Set copySheet = Worksheets("Sheet3")
Set pasteSheet = Worksheets("Sheet2")

For ii = 1 To 10
 copySheet.Range("A1:E3").Copy
 pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 Application.ScreenUpdating = True


Dim j, k, L, b As String
   Dim i As Long
   Dim wb As Workbook
   Dim sht1 As Worksheet
   Dim sht2 As Worksheet

  Set wb = ThisWorkbook
  Set sht1 = wb.Sheets("Sheet1")
  Set sht2 = wb.Sheets("Sheet2")

   j = "Name"
   b = "City"

   For i = 1 To 3

    k = sht1.Range("A" & i)
    L = sht1.Range("B" & i)

    sht2.Cells.Replace what:=j, replacement:=k, lookat:=xlWhole, MatchCase:=False
    sht2.Cells.Replace what:=b, replacement:=L, lookat:=xlWhole, MatchCase:=False

   Next i

Next ii

 End Sub

非常感谢任何指导。

Answer 1

这不是您所要求的，但您可以完全使用单元格公式来完成此任务。这有点复杂，VBA 可能是更好的方法，但如果您好奇的话:

我假设您的名称/城市对位于 Sheet1!$A$1:$B$9 **

在第 2 页的 A 列中，我将构建一个列表，指示要从哪一行获取数据。 (1,1,1,2,2,2,3,3,3,4,4,4 等)

在第 2 页的 B 列中，我将构建一个列表，显示此目标行应具有的结果类型(名称 - 城市名称为 1，名称城市 - 城市为 2 ，名称 - 城市为 3)有多种方法可以实现此目的。我用 =INT((ROW()-1)/3)+1 填充 A 列，用 =MOD(ROW()-1,3)+1 填充 B 列>

现在工作表 2 上的前两列看起来像

现在我可以使用它们来针对原始数据构建索引查找。 C 列将有 2 种不同类型的行为。它始终以名称开头，但如果 B 列为 2，则后面跟着城市。

=INDEX(工作表1!$A$1:$B$9,SA1,1)&IF(B1=2,""& INDEX(工作表1!$A$1:$B$9,A1,2), “”)

D 列也有两种类型的条目。始终以城市开头，但如果 B 列为 1，则后面加上名称。

=INDEX(工作表 1!$A$1:$B$9,A1,2)&IF(B1=1,""& INDEX(工作表 1!$A$1:$B$9,A1,1),"”)

瞧

如果您愿意，您可以将对 A 列和 B 列的所有引用替换为其中的实际公式。

那么 C 列将具有:

=INDEX(Sheet1!$A$1:$B$9,INT((ROW()-1)/3)+1,1)&" "&IF(MOD(ROW()-1,3)+1=2,INDEX(Sheet1!$A$1:$B$9,INT((ROW()-1)/3)+1,2),"")

在 D 列中进行相应替换，您可以省略 A 列和 B 列。

此方法的一个小不便之处在于，由于它依赖于 row()，因此如果不调整两个索引公式 ( INT((ROW ()-1)/3)+1 &MOD(ROW()-1,3)+1)

<小时/>

** 在现实世界中，我还会为您的源数据表命名，例如 NameCityTable 然后您的第一个结果列可能如下所示:

=INDEX(NameCityTable,INT((ROW()-1)/3)+1,1)&" "&IF(MOD(ROW()-1,3)+1=2,INDEX(NameCityTable,INT((ROW()-1)/3)+1,2),"")