使用 VBA 循环访问字符串的动态列表并创建摘要表-解网

问：

我有一个字符串列表，其长度可能因数据而异。我需要通过在新工作表中使用列表中的项目创建一行，然后使用将硬编码到 VBA 中的额外信息行来生成此列表的报告。到目前为止，我的方法是遍历列表，但我不熟悉 VBA 中的循环，因此正在寻找一些指导。

下面是源和目标数据示例

源

| Column A |
| -------- |
|  1       |
|  2       |
|  3       |
|  4       |
|  etc.    |

目的地

| Column A | Column B |
| -------- | -------- |
| 1        | Total    |
|          | a        |
|          | b        |
|          | c        |
| 2        | Total    |
|          | a        |
|          | b        |
|          | c        |

等。。

总值 a、b、c 等将是数据手册中计算的值例如，a = 值，b = 值 * 0.3

下面是我的示例VBA代码，我在哪里开始以及我希望它做什么。

Sub testloop()

Dim counter As Integer
Dim rng As Range
Dim cell As Range
Dim source As Worksheet, destination As Worksheet

source = Worksheets("Sheet1")
destination = Worksheets("Sheet2")
'I have included a counter as my initial research looks to use this logic to stop a loop when it has reached the end of the data
counter = 1
'Not sure if this will loop the whole of column A or just the rows with data
rng = Range("A:A")

For Each cell In rng
    counter = counter + 1
'Now I want to print the data from source into destination
'I am aware the following example code is not correct. Hopefully this illustates my requirement
'e.g.
'copy from "Source.Range(A) & counter"
'paste "Destination.Range (A) & counter

'followed by the next information
'insert "Total" into destination.Range (B) & counter
'insert "a" into destination.Range (B) & counter + 1
'insert "b" into destination.Range (B) & counter + 2
'insert "b" into destination.Range (B) & counter + 3

'^^ Maybe I need a counter2 variable here?

'Then repeat this

Next cell

End Sub

Excel VBA 循环 foreach

Sub testloop()
Dim counter As Integer
Dim rng As Range
Dim cell As Range
Dim source As Worksheet, destination As Worksheet
    
    Set source = Worksheets("Sheet1")
    Set destination = Worksheets("Sheet2")
    
    Set rng = source.Range("A1:A" & Range("A1").End(xlDown).Row)
    For counter = 0 To rng.Rows.Count - 1
        With destination.Range("A1").Offset(counter * 4)
            .Value = rng.Rows(counter + 1).Value
            .Offset(, 1) = "Total"
            .Offset(1, 1) = "a"
            .Offset(2, 1) = "b"
            .Offset(3, 1) = "c"
        End With
    Next counter
End Sub

使用 VBA 循环访问字符串的动态列表并创建摘要表

Using VBA to loop through a dynamic list of strings and create a summary sheet

评论

评论