VBA根据单元格中的值迭代行

Question

我想让我的 VBA 脚本按照另一个工作表中的实例值的顺序编写一段文本。我有一段代码将一大段文本写入工作表 A。

Dim selectedTest As String
Dim activeCell As Range
Dim outputCell As Range
Dim currentValue As String
Dim activePage As String
Dim row As String
Dim instancecol As String

selectedTest = template.Range("I6, I6").value
Set activeCell = template.Cells.Find(selectedTest + " Data")
Set activeCell = activeCell.Offset(0, 1)
instancecol = Split(activeCell(1).Address(1, 0), "$")(0)
Set activeCell = activeCell.Offset(2, -1)
currentValue = activeCell.value
row = activeCell.row
activePage = template.Range("B" + row)

Set outputCell = json.Range("D26")

outputCell.Activate
outputCell.value = Chr(34) + "name" + Chr(34) + ": " + Chr(34) + activePage + Chr(34) + ","
Set outputCell = outputCell.Offset(1, 0)
outputCell.value = Chr(34) + "instance" + Chr(34) + ": " + Chr(34) + "1" + Chr(34) + ","
Set outputCell = outputCell.Offset(1, 0)
outputCell.value = Chr(34) + "Input" + Chr(34) + ": ["
Set outputCell = outputCell.Offset(0, 1)

While Not currentValue = "ENDPARSE"

If Not (activeCell.Offset(0, 1).value = "") Then

    Dim currentPage As String
    Dim referenceType As String
    Dim reference As String
    Dim action As String
    Dim wait As String
    Dim screenshot As String
    Dim instance As String

    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = "{"
    Set outputCell = outputCell.Offset(1, 1)


    row = activeCell.row
    currentPage = template.Range("B" + row)

    If Not (activePage = currentPage) Then
        activePage = currentPage
        Set outputCell = outputCell.Offset(-1, -1)
        outputCell.value = ""
        Set outputCell = outputCell.Offset(-1, 0)
        outputCell.value = "}"
        Set outputCell = outputCell.Offset(1, -1)
        outputCell.value = "]"
        Set outputCell = outputCell.Offset(1, -1)
        outputCell.value = "},"
        Set outputCell = outputCell.Offset(1, 0)
        outputCell.value = "{"
        Set outputCell = outputCell.Offset(1, 1)
        outputCell.value = Chr(34) + "name" + Chr(34) + ": " + Chr(34) + activePage + Chr(34) + ","
        Set outputCell = outputCell.Offset(1, 0)
        outputCell.value = Chr(34) + "instance" + Chr(34) + ": " + Chr(34) + "1" + Chr(34) + ","
        Set outputCell = outputCell.Offset(1, 0)
        outputCell.value = Chr(34) + "Input" + Chr(34) + ": ["
        Set outputCell = outputCell.Offset(1, 1)
        outputCell.value = "{"
        Set outputCell = outputCell.Offset(1, 1)
    End If

    referenceType = template.Range("C" + row)
    reference = template.Range("A" + row)
    action = template.Range("F" + row)
    wait = template.Range("H" + row)
    screenshot = template.Range("I" + row)
    instance = template.Range(instancecol + row)
    currentValue = activeCell.value

    outputCell.value = Chr(34) + "type" + Chr(34) + ": " + Chr(34) + referenceType + Chr(34) + ","
    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = Chr(34) + "reference" + Chr(34) + ": " + Chr(34) + reference + Chr(34) + ","
    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = Chr(34) + "action" + Chr(34) + ": " + Chr(34) + action + Chr(34) + ","
    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = Chr(34) + "instance" + Chr(34) + ": " + Chr(34) + instance + Chr(34) + ","
    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = Chr(34) + "wait" + Chr(34) + ": " + Chr(34) + wait + Chr(34) + ","

    If Not (currentValue = "") Then
        Set outputCell = outputCell.Offset(1, 0)
        outputCell.value = Chr(34) + "value" + Chr(34) + ": " + Chr(34) + currentValue + Chr(34) + ","
    End If

    Set outputCell = outputCell.Offset(1, 0)
    outputCell.value = Chr(34) + "screenshot" + Chr(34) + ": " + Chr(34) + screenshot + Chr(34)

    Set outputCell = outputCell.Offset(1, -1)
    outputCell.value = "},"

End If

Set activeCell = activeCell.Offset(1, 0)
currentValue = activeCell.value
Wend

outputCell.value = "}"
Set outputCell = outputCell.Offset(1, -1)
outputCell.value = "]"
Set outputCell = outputCell.Offset(1, -1)
outputCell.value = "}"
Set outputCell = outputCell.Offset(1, -1)
outputCell.value = "]"
Set outputCell = outputCell.Offset(1, -1)
outputCell.value = "}"

Dim dataRange As Range
Set dataRange = json.UsedRange
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(template.Range("I2,I2") + "\\" + template.Range("I3,I3") + ".json", True)
For Each C In dataRange
a.WriteLine (C.value)
Next C
a.Close

template.Activate

End Sub

这从工作表 B 中获取值以将信息写入工作表 A。目前我的输出如下所示:

{           
"name": "Search",       
"instance": "1",        
"Input": [      
    {   
        "type": "button",
        "reference": "Search",
        "action": "Click",
        "instance": "1",
        "wait": "10",
        "screenshot": "true"
    },  
    {   
        "type": "dropdown",
        "reference": "PostcodeLogic",
        "action": "SelectByText",
        "instance": "2",
        "wait": "10",
        "value": "Is",
        "screenshot": "true"
    },  
    {   
        "type": "text",
        "reference": "PostCodeInput",
        "action": "SendKeys",
        "instance": "3",
        "wait": "10",
        "value": "AL2 4ED",
        "screenshot": "true"
    },  
    {   
        "type": "button",
        "reference": "Search",
        "action": "Click",
        "instance": "5",
        "wait": "10",
        "screenshot": "true"
    },  
    {   
        "type": "link",
        "reference": "ClientName ",
        "action": "Click",
        "instance": "4",
        "wait": "10",
        "screenshot": "true"
    }   
]       
}           

如您所见，我想要的所有信息都在那里并且可以正常工作。我现在需要让它按照 instance 的顺序写出测试 block .我有一个名为 sequence 的字段读取然后编号并将其放入 instance field 。

抱歉，如果没有很好地解释这一点，对 VBA 来说仍然很新。

Answer 1

像这样的东西，没有作为答案发布，只是为了使用代码格式。我在 A 中有我的数据，在 B 中有序列号

Sub testing()

Dim OutputCollection As New Collection                  '   For ease
Dim intMaxSeq As Integer
Dim intSeq As Integer
Dim intRow As Integer

intMaxSeq = WorksheetFunction.Max(Range("N:N"))

For intSeq = 1 To intMaxSeq

    For intRow = 1 To Range("A1").End(xlDown).Row

        If Range("N" & intRow) = intSeq Then
            '   Your code will sit here to build the o/p

            '   Like this example only!!!!!!!!
            sheet("Output").range("a1").value=range("A" & introw).value
            sheet("Output").range("b1").value=range("C" & introw).value
            sheet("Output").range("c1").value=range("J" & introw).value
            '



        End If

    Next intRow

Next intSeq

End Sub