在 VBA 中查找一个术语的首次出现，然后再出现另一个术语

Question

我正在尝试从已复制到MS Excel的.xml文档中提取信息。

结构如下(Excel中每行在不同的行中(：

<chapterid="chapter1">
<value1>123</value1>
<value2>456</value2>
</chapter>
<chapterid="chapter1">
<value1>789</value1>
<value2>012</value2>
</chapter>

我正在尝试指定value1(或value2(和chapterid，以找到<chapterid="chapter1">后第一次出现value1。

(它还必须能够在不同的工作表中运行，因此是以下函数中的第二个变量。我搜索的活动Worksheet称为mysheet，我现在正在尝试将结果放在此工作表的cells(2,2)中。

这是我到目前为止的代码：

Sub extractreport()
Dim chapterid As String
Dim mysheet As String
Dim myvar as String
tradeid = "Chapter1"
mansheet = "Test2"
myvar = "value1"    
Worksheets(mysheet).Cells(2, 2).Value = FindContentAfter(chapterid, mysheet, myvar)
End Sub

我对Function的尝试如下：

Public Function FindContentAfter(chapterid As String, mysheet As String, myvar As String)
Dim foundcell As Range
Set wb = Workbooks("ThisWorkbook")
Dim rowfind As Range
Dim adjstring As String
adjstring = "<chapterid=" & Chr(34) & chapterid & Chr(34) & ">"
MsgBox adjstring 'searching for the beginning of the chapter, because the String "Chapter1" can occur in different `values` in other chapters.
Set rowfind = Worksheets(mysheet).Range("A:A").find(What:=adjstring, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False) 'find row in which chapter begins
Set foundcell = Worksheets(mysheet).Range("A:A").find(What:=mystring, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, after:=Worksheets(mysheet).Range(rowfind)) 'find first myvar after the previously found row
If Not foundcell Is Nothing Then
'MsgBox (mystring & " found in row: " & foundcell.Row)
Else
'MsgBox (mystring & " not found")
End If
MsgBox "1    " & foundcell
FindContentAfter = Mid(foundcell.Value, InStr(foundcell.Value, ">") + 1, InStr(3, foundcell.Value, "<") - InStr(foundcell.Value, ">") - 1)
End Function

它适用于adjstring的定义和搜索。但是，查找rowfind(作为本章的开头(并将其作为搜索myvar的起点是行不通的。

错误是"运行时错误 1004"。我尝试了各种方法，例如：

Set foundcell = Worksheets(mysheet).Range("A" & rowfind.Row() & ":A").find(What:=mystring, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)

，或Range(rowfind).Row()，但我无法克服这个问题。

Answer 1

如果我正确理解您的帖子，您正在尝试获取<value1>123</value1>之间的文本，因此在您的情况下，您正在尝试获取123.

如果正确，则需要将公式修改为以下公式：

FindContentAfter = Mid(foundcell.Value, InStr(foundcell.Value, ">") + 1, InStrRev(foundcell.Value, "<") - InStr(foundcell.Value, ">") - 1).

另外，在您的第二个Find中，参数After正在寻找一个范围，因此在这种情况下，我们使用第一个Find中的rowfindRange对象。

尝试将代码修改为下面的代码，代码注释中的解释：

Option Explicit
Sub extractreport()
Dim chapterid As String, mysheet As String, myvar As String
chapterid = "Chapter1"
mysheet = "Test2"
myvar = "value1"
Worksheets(mysheet).Cells(2, 2).Value = FindContentAfter(chapterid, mysheet, myvar)
End Sub
'===================================================================================
Public Function FindContentAfter(chapterid As String, mysheet As String, myvar As String)
Dim foundcell As Range
Dim rowfind As Range
Dim adjstring As String
adjstring = "<chapterid=" & Chr(34) & chapterid & Chr(34) & ">"
MsgBox adjstring 'searching for the beginning of the chapter, because the String "Chapter1" can occur in different `values` in other chapters.
With Worksheets(mysheet)
Set rowfind = .Range("A:A").Find(What:=adjstring, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False) ' find the range where the chapter begins
' conmfirm Chapter was found
If rowfind Is Nothing Then
MsgBox "Unable to find " & adjstring & " in worksheet " & .Name
Exit Function
End If
Set foundcell = .Range("A:A").Find(What:=myvar, After:=rowfind, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False) 'find first myvar after the previously found row
If Not foundcell Is Nothing Then
MsgBox myvar & " found in row: " & foundcell.Row
Else
MsgBox myvar & " not found"
End If
MsgBox "1    " & foundcell
End With
FindContentAfter = Mid(foundcell.Value, InStr(foundcell.Value, ">") + 1, InStrRev(foundcell.Value, "<") - InStr(foundcell.Value, ">") - 1)
End Function

Answer 2

你的主要问题是(之后(参数，

将代码更改为以下内容：

Sub extractreport()
Dim chapterid As String
Dim mysheet As String
Dim myvar As String
chapterid = "Chapter1"
mysheet = "Test2"
myvar = "value1"
Worksheets(mysheet).Cells(2, 2).Value = FindContentAfter(chapterid, mysheet, myvar)
End Sub

Public Function FindContentAfter(chapterid As String, mysheet As String, myvar As String)
Dim wb As Workbook
Dim foundcell As Range
'Set wb = Workbooks("ThisWorkbook")
Dim rowfind As Range
Dim adjstring As String
adjstring = "<chapterid=" & Chr(34) & chapterid & Chr(34) & ">"
'MsgBox adjstring 'searching for the beginning of the chapter, because the String "Chapter1" can occur in different `values` in other chapters.
Set rowfind = Worksheets(mysheet).Range("A:A").Find(What:=adjstring, Lookat:=xlPart, MatchCase:=False, SearchFormat:=False) 'find row in which chapter begins           
Set foundcell = Worksheets(mysheet).Range("A:A").Find(What:=myvar, Lookat:=xlPart, MatchCase:=False, SearchFormat:=False, after:=rowfind)  'find first myvar after the previously found row
If Not foundcell Is Nothing Then
MsgBox (myvar & " found in row: " & foundcell.Row)
Else
MsgBox (myvar & " not found")
End If
MsgBox "1    " & foundcell
'FindContentAfter = Mid(foundcell.Value, InStr(foundcell.Value, ">") + 1, InStr(3, foundcell.Value, "<") - InStr(foundcell.Value, ">") - 1)
End Function