我尝试在下面的一个性能高得多的数组循环中逐行转换当前循环,但失败了。
我面临的问题是,结果与行循环不匹配。
我需要从这个当前循环中更改什么来将其转换为对给定输出表现相同的数组循环?
为了提供一点背景信息,它在一个范围内迭代(如果值不为空(,形成一个字符串格式的非常简单的xml
像这样:
<rows><r><i>0</i><i></i><i>3495776</i><i>0200</i><i>DF-252</i><i></i></r><r><i>0</i><i></i><i>3495777</i><i>0200</i><i>DF-252</i><i></i></r></rows>
Dim rData As Variant
Dim rRow As Range
Dim i As Long
Dim xmlData As String
Dim strText As String
'-- Read the data --
Set rData = Worksheets("ProtectedSheet").Range("D2:I1048576")
For Each rRow In rData.Rows
If Not rRow Is Nothing Then
If Len(Trim(rRow.Cells(1).Value2)) > 0 Then
xmlData = xmlData + "<r>"
For i = 1 To rRow.Cells.Count
strText = rRow.Cells(i).Value2
xmlData = xmlData + "<i>" + strText + "</i>"
Next i
xmlData = xmlData + "</r>"
Else
Exit For
End If
End If
Next rRow
xmlData = "<rows>" + xmlData + "</rows>"
这应该可以实现您想要的:
Sub test()
Dim arrData As Variant
Dim rRow As Long
Dim rCell As Long
Dim i As Long
Dim xmlData As String
Dim strText As String
'-- Read the data into an array --> much better performance
arrData = ActiveSheet.Range("A2:C1000") '>>> adjust to your needs
For rRow = 1 To UBound(arrData, 1)
If Len(Trim(arrData(rRow, 1))) > 0 Then 'only read rows where first cell <> empty
xmlData = xmlData + "<r>"
For rCell = 1 To UBound(arrData, 2)
strText = Trim(arrData(rRow, rCell))
xmlData = xmlData + "<i>" + strText + "</i>"
Next
xmlData = xmlData + "</r>"
End If
Next
xmlData = "<rows>" + xmlData + "</rows>"
End Sub
当一行的第一个单元格为空时,您的代码将退出for循环,并且不会处理其余的行。
此外:您应该将数据读取到数组(变体(中,因为这将比for下一个循环读取的速度快得多。
顺便说一句,你也可以用一个公式来实现这一点:
=VSTACK("<rows>",
BYROW(A2:C1000001,
LAMBDA(r,"<r><i>"&TEXTJOIN("</i><i>",FALSE,r)&"</i></r>")),
"</rows>")
只需从此更新i
变量:
Dim i As Long
对此:
Dim i As Variant
并从此更新你的For Loop:
For i = 1 To rRow.Cells.Count
strText = rRow.Cells(i).Value2
xmlData = xmlData + "<i>" + strText + "</i>"
Next i
对此:
For Each i In rRow.Value2
xmlData = xmlData + "<i>" + CStr(i) + "</i>"
Next i
此循环将考虑该行中的所有值。