时间戳

首页 > 解决方案 > VBA将另一个变量添加到循环中

excel - VBA将另一个变量添加到循环中

问题描述

我有一个方法CreatePage()。我想循环变量以通过方法。我设法让它适用于一个变量：

Sub main()

   Dim rng As Range
   Dim aSht As Worksheet
    
   Set aSht = ThisWorkbook.Sheets("Sheet1")
    
   For Each rng In aSht.Range("A5:A8")
        CreatePage(rng)
    Next rng

End Sub

我需要它循环如下：
CreatePage("A5", "C6")
CreatePage("A6", "C7")
CreatePage("A7", "C8"), ...

我被困在如何通过两个变量。这是我到目前为止所取得的成就，但我认为我的方向并不正确：

Sub main()

   Dim rng As Range
   Dim rng2 As Range
   
   Dim aSht As Worksheet
   Dim bSht As Worksheet
    
   Set aSht = ThisWorkbook.Sheets("Sheet1")
   Set bSht = ThisWorkbook.Sheets("Sheet2")
    
   For Each rng In aSht.Range("A5:A8")
        For Each rng2 In bSht.Range("C6:C9")
             CreatePage(rng,rng2)
    Next rng, rng2

End Sub

我已经调整CreatePage()为持有两个变量。我对在 for 循环中获取第二个变量感到困惑。任何人都可以帮忙吗？

标签： excelvba

解决方案

嵌套的 for 循环在这里无济于事。您只需要循环一个范围并从另一个范围中选择所需的单元格

Sub main()
    Dim rng As Range
    Dim rng2 As Range
   
    Dim aSht As Worksheet
    Dim bSht As Worksheet
    
    Set aSht = ThisWorkbook.Sheets("Sheet1")
    Set bSht = ThisWorkbook.Sheets("Sheet2")
    
    Dim idx As Long
    Set rng = aSht.Range("A5:A8")
    Set rng2 = bSht.Range("C6:C9")
    'You might want to add code here to check the size and shape of your ranges
    For idx = 1 To rng.Cells.Count
        CreatePage rng.Cells(idx), rng2.Cells(idx)
    Next

End Sub

推荐阅读