Excel VBA finding a text and return row number (loop)

Question

I am finding a difficulty in probably a rather simple code to return the corresponding row numbers which contain a specific text in column("A:A"). Have been trying several ways but without success - it does not loop through the last row. Here is what I have got so far:

Dim rowCount As Integer
Dim i As Integer
Dim FindRow As Variant
Dim RowN As Integer
Dim blockSize As Integer

rowCount = Range("A1").CurrentRegion.Rows.Count
 
For i = 2 To rowCount 
    
    Set FindRow = Cells(i, 1).Find(What:="group: 1", LookAt:=xlPart, SearchOrder:=xlByRows)
    RowN = FindRow.Row
    MsgBox RowN

    If RowN > 1 Then
    blockSize = FindRow.Row - 1
    MsgBox blockSize
    End If

Next i

Darren Bartrup-Cook · Accepted Answer · 2021-02-06 19:19:34Z

2

Your Find is only searching one cell in the region. Using that method you should use Instr to check each cell.

The Find method will find the first occurrence in a range, and FindNext will find subsequent occurrences.

Option Explicit
    
Sub Test()

    Dim lastrow As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Dim srchRange As Range
        Set srchRange = .Range(.Cells(1, 1), .Cells(lastrow, 1))
    End With
    
    With srchRange
        Dim rFound As Range
        Set rFound = .Find("group: 1", .Cells(1, 1), xlValues, xlPart, , xlNext, False)
        If Not rFound Is Nothing Then
            Dim firstAdd As String
            firstAdd = rFound.Address
            
            Dim FoundRows As String
            Dim blocksize As Long
                
            Do
                If rFound.Row > 1 Then
                    blocksize = rFound.Row - 1
                    'other code.
                    
                End If
                Set rFound = .FindNext(rFound)
            Loop Until rFound.Address = firstAdd
        
        End If
    End With

End Sub

If you wanted to add more flexibility to the process you could rewrite it as a function so you can search different groups, different columns and different sheets.

Sub Test1()

    Dim Result As Variant
    Result = GetBlocks(, , ThisWorkbook.Worksheets("Sheet1"))
    
    If IsEmpty(Result) Then
        MsgBox "No groups found."
    Else
        Dim itm As Variant
        For Each itm In Result
            MsgBox itm
        Next itm
    End If

End Sub

Function GetBlocks(Optional GroupID As String = "group: 1", _
                   Optional ColNum As Long = 1, _
                   Optional wrkSht As Worksheet) As Variant
                   
    'Optional arguments must be constant expressions, so a
    'default worksheet can't be set before here.
    If wrkSht Is Nothing Then Set wrkSht = ActiveSheet

    'Define the range to be searched.
    With wrkSht
        Dim lastrow As Long
        lastrow = .Cells(.Rows.Count, ColNum).End(xlUp).Row
        
        Dim srchRange As Range
        Set srchRange = .Range(.Cells(1, ColNum), .Cells(lastrow, ColNum))
    End With
    
    With srchRange
        Dim rFound As Range
        Set rFound = .Find(GroupID, .Cells(1, 1), xlValues, xlPart, , xlNext, False)
        If Not rFound Is Nothing Then
            Dim firstAdd As String
            firstAdd = rFound.Address
            
            'Create a string of row numbers.
            'e.g. 4,6,8,11,13,14,16,17, < note final comma.
            Dim FoundRows As String
            Do
                If rFound.Row > 1 Then
                    FoundRows = FoundRows & rFound.Row & ","
                End If
                Set rFound = .FindNext(rFound)
            Loop Until rFound.Address = firstAdd
            
            'Split string into arrow of row numbers.
            'These will be string data types.
            Dim tmp As Variant
            tmp = Split(FoundRows, ",")
            
            'Convert string to long data type.
            ReDim tmp1(0 To UBound(tmp) - 1)
            Dim x As Long
            For x = 0 To UBound(tmp1)
                tmp1(x) = CLng(tmp(x))
            Next x
            
            'Return result of function.
            GetBlocks = tmp1
            
        End If
    End With

End Function

edited Feb 6, 2021 at 19:19

answered Jan 31, 2021 at 16:28

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VBasic2008 Over a year ago

Since the parameter for SearchOrder is omitted, you could also omit the parameters for After (.Cells(1, 1)), SearchDirection (xlNext) and MatchCase (False), since they are the default values. Keep in mind that the LookIn parameter xlValues will cause the Find method to fail finding the value in hidden rows (easily tested), therefore xlFormulas is mostly preferred.

Makiko Over a year ago

Thank you for your advice - very helpful! It does work perfectly to obtain the row numbers. However, I need to use the row number as an integer for following set of commands (one written above "blockSize = FindRow.Row - 1"). Is there anyway that I can get the row number as an integer value?

Darren Bartrup-Cook Over a year ago

Have updated the code so it shows the row number each time. Not sure of your final definition of a block size - just the row number before the keyword is found?

Collectives™ on Stack Overflow

Excel VBA finding a text and return row number (loop)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related