作为VBA的基本用户，我想知道是否有类似于可以应用于集合元素的偏移函数的东西。它是否存在，还是需要作为用户定义的函数创建?我想从类似于已接受的答案(再次感谢Tim)中的代码开始，回答以下问题:根据.csv文件更新.xml文档。为简单起见，假设我必须在.csv文件中执行查找操作，我将使用下面的函数，它将是一个集合对象。
Sub editxml()
Dim Obj As MSXML2.DOMDocument
Dim xmlpath As String
Dim Node As IXMLDOMNodeList
Dim Nm As IXMLDOMNode
Dim thing As Object, q As Object
Dim wb As Workbook, ws As Worksheet
Dim matches As Collection
Set Obj = New DOMDocument
Obj.async = False
Obj.validateOnParse = False
xmlpath = "C:UsersxxxDesktopppp.xml"
Obj.SetProperty "SelectionNamespaces", "xmlns:ns0='http://update.DocumentTypes.Schema.ppp.Xml'"
If Obj.Load(xmlpath) = True Then
MsgBox "File XML uploaded"
Else
MsgBox "File XML not uploaded"
Exit Sub
End If
'open the CSV file
Set wb = Workbooks.Open("C:UsersxxxDesktopmycopy.csv")
Set ws = wb.Worksheets(1)
Set Node = Obj.DocumentElement.SelectNodes("AA/BB/CC/DD")
For Each Nm In Node
Set thing = Nm.SelectSingleNode("thing")
Set q = Nm.SelectSingleNode("qt")
'moved the Find logic to a standalone function
Set matches = FindAll(ws.Range("AR:AR"), thing.Text)
'did we get any matches in the range?
If matches.Count > 0 Then
'This section of the code should perform some computations according to the value of a particular cell in a different column, so basically it should offset the element of the collection.
q.Text = "do somewhat else"
End If
Next
Obj.Save xmlpath
End Sub
'find all matching cells in a range and return them in a Collection
Public Function FindAll(rng As Range, val As String) As Collection
Dim rv As New Collection, f As Range, addr As String
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not f Is Nothing Then addr = f.Address() 'store first cell found
Do Until f Is Nothing
rv.Add f
Set f = rng.FindNext(after:=f)
If f.Address() = addr Then Exit Do 'exit if we've looped back to first cell
Loop
Set FindAll = rv
End Function
我知道偏移函数可以应用于VBA中的范围对象。如果我必须处理范围，我会在代码的缺失部分做类似的事情，但这显然不起作用。我想保持对象匹配作为一个集合，因为它对我的目的更灵活。
If matches.Count > 0 Then
'This section of the code should perform some computations according to the value of a particular cell in a different column, so basically it should offset the element of the collection.
q.Text = matches.offset(0,-3).value*matches.offset(0,-6)
End If
Next
更新:我想在我的。csv文件的单元格中的值之间执行一些计算。对于集合中的每个元素，也就是地址，假设匹配的第一个元素是AR2，我必须做D2*S2这样的事情。基本上，我必须从函数的结果开始，按列"移动"，留在同一行。这就是offset的含义。
由于集合是一个范围集合，因此必须循环遍历它们:
If matches.Count > 0 Then
Dim match As Range
For Each match in matches
q.Text = match.Offset(0, -3).Value * match.Offset(0, -6).Value
Next match
End If
注意:q.Text只保存集合中的最后一个值。这是有效的，如果你只有一个匹配，但我不确定你想做什么，当有多个匹配。