Friday, 22 May 2015

How to use Excel VBA Instr Function

Excel VBA Instr Function Syntax and Example
The INSTR function returns the position of the first occurrence of a Substring in a main String from the search position.

Syntax :
InStr( [Search Start Pos], String, Substring, [Compare] )

Here :
[Search Start Pos] :
Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
String : The string to search within.
Substring :The Substring that you want to find in the String.
[Compare] : It is Optional. It is the type of comparison to perform. It can be one of the following values:


Example :
Suppose I have data Range as shown below :


From above data range 'MyRange' , I want search the String 'RED' , and return the Matching Cell addresses in a separate column as shown below.

You can do the above task using the below Macro :

Sub InStr_Search()
Dim MyRng As Range

Set MyRange = ActiveSheet.Range("MyData")
Str_Search = "RED"
Z = 2

For Each Cell In MyRange
C = Cell.Column

If InStr(1, Cell, Str_Search, 1) Then
Range("D" & Z) = "Found at: " & Cell.Address
Z = Z + 1
End If

Next Cell
End Sub
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

2 comments:

  1. I don't ordinarily comment but I gotta say appreciate it for the post on this great one :
    D.

    ReplyDelete
  2. Keep on writing, great job!

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts