Friday, 22 May 2015

Excel VBA Instr Function Syntax and Examples

Instr Function
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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts