Requirement:
The user wants to check a range of cells for a text, text is in one of the cells, and wants excel to be able to identify which row the text is in. Range is from columns "AB:BB".
The user just put 500 in the loop just to count down the rows. The range is not beyond row 500.
Here is the user's current 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
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by AlanSidman.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to add columns in excel |
| How to use Excel VBA macro to import data from multiple workbooks to main workbook |
| How to use IF/Then in VBA code |
| How to use Excel VBA to delete rows in a column based on a range of cells in another column |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.