Excel VBA - Selecting the range that ends when the closest out of three searched strings occurs

Question

I am trying to select a range of cells between two terms. The first term is clearly set, the last one must be nearest, the first out of three possible terms. I tried to adapt the solution from here:

Using an Or function within Range.Find VBA

My range starts with a certain term. But it may end with at least one of three different terms. I would like to select a range between my searched word and the closest of the three ending terms.

So far my code looks as follows.

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 above 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

My question, how to set the end of range (the code area with question marks?)

I am a beginner and maybe the solution is simple, I tried help files and other portals. but I could not find a good solution. I hope my question will be useful for the community.

Please let me know if more is required to this question. Thank you for all your help.

Mark Balhoff · Accepted Answer · 2014-06-18T13:08:31.827

I siphoned off that logic into another function that maintains a reference to foundEnd and updates that reference if we find something closer than previous tries. It also returns an updated row difference between Range A and the current closest end range. The object here is to check each possible end range for two things: Empty and if Empty, is it closer than all other ranges checked. I think the code should be fairly self explanatory. If your spreadsheet is large you might need to crank up that initial 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

Dear Mark, it works quite well. However I noticed a case when nothing is found and the end of data area is reached. Is it possible for your code to include this case? When no word is found, so that the range starts from foundA and ends with the end of the data in a sheet? Thank you for the superb code! — Jacek Kotowski, Jun 17 '14 at 09:22
@JacekKotowski Okay I added the three lines beneath the comment indicating such. I also gave two options. The one that will run right now gets the number of used rows in Excel to determine the endpoint. It assumes the data starts in Row A. The other method (commented out to the right of that line) is easier but assumes no empty cells in the column. Edits done now. — Mark Balhoff, Jun 17 '14 at 14:06
Dear Mark, I noticed another problem. It finds the closest match both downwards and upwards. Therefore if it finds the foundA and finds the closest term above found A it will get the data above foundA. But the interesting data in this case starts from foundA to the end of data. Is it possible to modify perhaps the public function to look at the closest foundB, foundC, foundD or found end of data below and never above foundA? I tried to replace searchDirection to xlDown but it did not change anything. Thank you very much for help. — Jacek Kotowski, Jun 18 '14 at 09:20
@JacekKotowski Yes Excel loops back up to the top if it doesn't find it below. I just fixed that in the above code by adding 'And tmpRng.Row > startRng.Row' to one of the if statements. — Mark Balhoff, Jun 18 '14 at 13:10
Dear Mark It works... it works. Like a charm. How can I express my gratitude! My reputation is below the level to permit me for issuing plusses. But it is a piece of a very useful code. Thank you. You saved me a lot of work. I wish you all the best. — Jacek Kotowski, Jun 18 '14 at 14:46

Excel VBA - Selecting the range that ends when the closest out of three searched strings occurs

1 Answers1