从VBA并集中选择元素

Question

我有一项任务要根据.xls文件中的几个部分制作VBA宏。我知道在这个文件中，总是有三个部分以示例文件"中的特定名称开头；块"；。但是起始行中"；块"；每次写的都可能不同。

.xls文件示例：在此处输入图像描述

我的方法是搜索包含字符串"的每一列的地址；块"；然后在知道每个块的起始位置的基础上编写进一步的代码。到目前为止我的代码：

Public Values
Sub Macro1()
FindAll ("Block")
Debug.Print Values
'    
End Sub
Sub FindAll(text)
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
fnd = text
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)

'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)

'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do

Loop
'Creates global value with all found adresses
Values = rng.Address

Exit Sub

按预期接收输出：

$A$5,$A$8,$A$1

然而，我很难选择元素进行进一步的编码。我试过了：

Debug.Print Values.Rows.item(1).Adress
Debug.Print Values.Rows.item(1,1).Adress
Debug.Print Values.Rows.item(1)
Debug.Print Values.Rows.item(1,1)

但它产生了"；运行时错误"424"；

我想要的输出是创建三个变量，其中包含这些部分的地址。

Debug.Print Section_1
Debug.Print Section_2
Debug.Print Section_3

收益率：

$A$1
$A$5
$A$8

有没有办法在VBA中从并集中选择第n个元素？

Answer 1

如果你想访问该范围的单个单元格，你可以简单地循环：

Dim cell as Range
For Each cell In rng
Debug.Print cell.Address
Next

也可以使用索引：

Dim i As Long
For i = 1 To rng.Count
Debug.Print rng(i).Address
Next

现在在您的示例中，使用并集组合单个单元格。如果合并较大的范围并希望访问这些范围，则可以使用Areas-属性。但是，Excel将优化区域，如果执行Union(Range("A1"), Range("A2))，则最终会得到一个区域A1:A2。

With ActiveSheet
Set rng = Union(.Range("D5:E16"), .Range("A1:F12"), .Range("X4"))
End With
Dim a As Range
For Each a In rng.Areas
Debug.Print a.Address
Next
For i = 1 to rng.Areas.Count
Debug.Print rng(i).Address
Next

顺便说一句：每个范围(甚至是一个单元格(都有Areas-属性集，所以在一个范围的Areas上循环总是安全的。

Answer 2

Try，

Public Values
Public rngDB() As Range
Sub Macro1()
Dim i As Integer
FindAll ("Block")
Debug.Print Values

For i = LBound(rngDB) To UBound(rngDB)
Debug.Print rngDB(i).Address
Debug.Print rngDB(i).Cells(1).Address
Debug.Print rngDB(i).Cells(1, 2).Address
Debug.Print rngDB(i).Cells(2, 1).Address
Next i
End Sub
Sub FindAll(text)
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
Dim sAddress() As String
Dim n As Integer

fnd = text

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
'GoTo NothingFound
End If

Set rng = FoundCell

'Loop until cycled through all unique finds
Do
n = n + 1
ReDim Preserve rngDB(1 To n)
ReDim Preserve sAddress(1 To n)
Set rngDB(n) = FoundCell.CurrentRegion
sAddress(n) = rngDB(n).Address
Set FoundCell = myRange.FindNext(after:=FoundCell)

Loop While FoundCell.Address <> FirstFound

'Creates global value with all found adresses
If n Then
Values = Join(sAddress, ",")
End If

End Sub