Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

how to find text string in range get row and column using excel vba

Posted by | July 22, 2011 .

In Visual Basic for Application sometime we wanted to know whether specific keyword or text strings exist or not in used range before any action or activity. After check either true or false we need to know the row and column index. For example you need to know keyword “Title” in excel sheet then color the background with yellow. Below code will do the job for you.

Option Explicit

Sub FindSingleKeyword()
‘Written by hisham hadi
‘http://vbexcel.com

Dim uRange As Range
Dim FindTitle As Range
Dim tRow As Long
Dim tCol As Integer

Set uRange = ActiveSheet.UsedRange
Set FindTitle = uRange.Find("Title")

    If FindTitle Is Nothing Then
        MsgBox ("Sorry! No keyword title is found")
    Else
        tRow = FindTitle.Row
        tCol = FindTitle.Column
        MsgBox ("Yes! Title keyword found, Row index = " & _
        tRow & ",Column Index = " & tCol)
        Cells(tRow, tCol).Interior.ColorIndex = 6
    End If
End Sub

If you have multiple location of keyword “Title” then below code will do the job for you.

Sub FindMultiKeyword()
'Written by hisham hadi
'http://vbexcel.com

Dim Cell As Variant
Dim uRange As Range
Dim x As String
Dim CellTxt As String
Dim RngFindTitle As Range
Dim FindTitle As Integer
Dim tRow As Long
Dim tCol As Integer

x = "TITLE"
Set uRange = ActiveSheet.UsedRange
Set RngFindTitle = uRange.Find("Title")

If RngFindTitle Is Nothing Then
    MsgBox ("Sorry! No keyword title is found")
Else
    For Each Cell In uRange
        CellTxt = UCase(Cells(Cell.Row, Cell.Column).Text)
        FindTitle = InStr(1, CellTxt, x)
        If FindTitle <> 0 Then
            tRow = Cell.Row
            tCol = Cell.Column
            MsgBox ("Yes! Title keyword found, Row index = " & _
            tRow & ",Column Index = " & tCol)
            Cells(tRow, tCol).Interior.ColorIndex = 6
        End If
    Next
End If
End Sub

Please try copy the above code into module then run this macro. Enjoy your learning.

E-mail me if you have any project or question I will help for free.
Click here to E-Mail me

Thank.

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments

8 Comments so far
  1. Andrew Simpal July 22, 2011 8:25 am

    I am searching for this how to find text string in range get row and column using excel vba. Here I found solution. Thanks dear for this lovely help :)

  2. hisyam hardy July 23, 2011 9:00 am

    you’re welcome

  3. sunil April 10, 2012 4:20 am

    Good job done thanks.

    we can also use it for merging

    Rows(tRow).Merge

  4. Alex April 27, 2012 4:44 am

    Hi Hisham,

    I am working on a VBA code to search a text string in a sheet Column H:H. I have two sheets Main and Raw data sheet. On Main Sheet I have created a text box and two command buttons (SUBMIT and CLOSE). I want the user to enter text in the textbox and I want VBA to search that text in the raw data sheet and Publish the findings. I mainly want the complete row found with the text in raw data sheet to Main Sheet Column A24 Onwards.

    This will be a great help If you can help me with this.

  5. Ritu May 9, 2012 12:37 pm

    thanks a lot. was a gr8 help

  6. Alejandro May 13, 2012 4:48 am

    Great job!! This is by far what I was looking for. It took me long to find it. I was wondering, would it be easy to make the vba to write the rows and column number indexes in a Column of my worksheet, i.e.Column H:H?

  7. mikeem July 20, 2012 12:50 pm

    Works, thanks!

  8. aziz July 30, 2012 2:12 am

    Thanks for make my life easier…it works