excel-vba - Excel选择最接近三个搜索字符串时结束的范围

  显示原文与译文双语对照的内容
0 0

我正在尝试在两个术语之间选择一个单元格范围。 第一个术语是明确的,最后一个必须是最近的,第三个可能的术语中。 我尝试从这里调整解决方案:

使用 Range.Find VBA中的一个或者多个函数

我的范围从一个特定的角度开始。 但它可能以三种不同术语中的至少一个结束。 我想选择我搜索的单词和三个结束词中最接近的词之间的距离。

到目前为止,我的代码看起来如下。


Sub FindData(sheetName, termA)

Dim foundA As Range, _
 foundB As Range, _
 foundC As Range, _
 foundD As Range, _
 foundEnd As Range


With Sheets(sheetName).Columns(1)

Set foundA =. Find(termA)
If Not foundA Is Nothing Then

' my searched end names

Set foundB =. Find("End of table", After:=foundA, `SearchDirection:=xlNext)
Set foundC =. Find("Version", After:=foundA, `SearchDirection:=xlNext)
Set foundD =. Find("Next table", After:=foundA, `SearchDirection:=xlNext)

End If

If Not foundB Is Nothing And Not foundC And Not foundD Is Nothing Then


Set foundEnd =??????????????????????????????

'The original solution from the link 上面 is not applicable here 
'and displays errors, ' ie. 
'Set foundEnd = Range("A1").Cells(Application.Min(foundA.Row, foundB.Row, foundC.Row))


End If


Set foundA = foundA.Offset(3, 0)
Set foundEnd = foundEnd.Offset(-1, 3)


Range(foundA, foundEnd).Copy

EndSub

我的问题,如何设置范围的末尾( 带有问号的代码区域)?

我是初学者,也许解决方案很简单,我尝试帮助文件和其他门户。 但是我找不到一个好的解决方案。 我希望我的问题对社区有帮助。

如果这个问题需要更多的信息,请告诉我。 谢谢你们援手相助.

时间: 原作者:

0 0

我将这个逻辑抽出到另一个函数,保持了对foundEnd的引用,如果我们找到了以前的尝试。 它还返回范围A 和当前最近的结束范围之间的更新行差。 这里的对象是检查两个可能的结束范围: 空和空,是否比选中的所有其他区域更接近。 我认为代码应该是相当自我解释的。 如果电子表格很大,你可能需要启动初始 1000.


Sub FindData(sheetName As String, termA As String)
Dim foundA As Range, foundB As Range, foundC As Range, foundD As Range, foundEnd As Range
Dim currDiff As Long
With Sheets(sheetName).Columns(1)
 Set foundA =. Find(termA)
 If Not foundA Is Nothing Then
 currDiff = 1000
 currDiff = getRngRowDiff(currDiff, foundEnd, foundA,"End of table", sheetName)
 currDiff = getRngRowDiff(currDiff, foundEnd, foundA,"Version", sheetName)
 currDiff = getRngRowDiff(currDiff, foundEnd, foundA,"Next Table", sheetName)
 End If
 ''' Added Code to Handle Case Where No End Range Has Been Found
 If currDiff = 1000 Then
 Set foundEnd = Sheets(sheetName).Cells(Sheets(sheetName).UsedRange.Rows.Count, 1) 'foundA.End(xlDown)
 End If
End With
End Sub

Public Function getRngRowDiff(ByVal closestDiff As Integer, ByRef foundRng As Range, ByRef startRng As Range, ByVal searchTerm As String, sheetName As String) As Long
 Dim tmpRng As Range
 Set tmpRng = Sheets(sheetName).Columns(1).Find(searchTerm, After:=startRng, SearchDirection:=xlNext)
 getRngRowDiff = closestDiff
 If Not tmpRng Is Nothing Then
 If tmpRng.Row - startRng.Row <closestDiff And tmpRng.Row> startRng.Row Then
 getRngRowDiff = tmpRng.Row - startRng.Row
 Set foundRng = tmpRng
 End If
 End If
End Function

...