Excel VBA Check range for text

quanziee · Jul 11th 2018

Hi, I want to check a range of cells for a text, text is in one of the cells, and I want excel to be able to identify which row the text is in. Range is from columns "AB:BB". I just put 500 in the loop just to count down the rows. The range is not beyond row 500. Thanks.

Here is my current code:

Code

Sub FindMatchingText()
Dim i As Integer, TextToFind As String


TextToFind = "CA5"


For i = 1 To 500
       If Sheets("Hotel Booking").Cells(i, "AB" + i, "BB").Value = TextToFind Then
            MsgBox ("Found text on row" & i)
            Exit Sub
       End If
Next i


MsgBox ("Text not found")


End Sub

Display More

**alansidman** · Jul 11th 2018

Here is how I would do it.

Code

Option Explicit


Sub FindMatchingText()
Dim i As Integer, TextToFind As String
Dim lr As Long, c As Range, rng As Range
Dim ThisRow As Long
lr = Range("AB" & Rows.Count).End(xlUp).Row
Set rng = Range("AB1:BB" & lr)
TextToFind = "CA5"


For Each c In rng
If InStr(c, TextToFind) > 0 Then
ThisRow = c.Row
MsgBox ("Found text on row" & ThisRow)
Exit Sub
End If
Next c


MsgBox ("Text not found")


End Sub

Display More

quanziee · Jul 11th 2018

Thanks. How can I get it to loop and show me multiple msgboxes if "CA5" can be found on multiple rows?

**alansidman** · Jul 12th 2018

Remove the line Exit Sub

quanziee · Jul 12th 2018

oops, my bad

Excel VBA Check range for text

Participate now!

Share