The code below works - if string abcd is found in column E then it's position is printed in the same row in column X, if not found then it prints 0
Sub SearchInColumn()
Dim LastRow As Integer
Dim SrchIn As String
Dim SrchFor As String
LastRow = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow
SrchIn = Sheet1.Cells(i, 5).Value
SrchFor = "abcd"
'If SrchFor = "abcd" Then
Sheet1.Cells(i, "X").Value = InStr(SrchIn, SrchFor)
'End If
Next i
End Sub
Same code with if-statement does not work as shown below - nothing prints in column X, why? What is wrong with the if-statement?
Sub SearchInColumn()
Dim LastRow As Integer
Dim SrchIn As String
Dim SrchFor As String
LastRow = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow
SrchIn = Sheet1.Cells(i, 5).Value
'SrchFor = "abcd"
If SrchFor = "abcd" Then
Sheet1.Cells(i, "X").Value = InStr(SrchIn, SrchFor)
End If
Next i
End Sub
Looking at the two ways you are trying to search for text.
the first way works:
SrchFor = "abcd"
Sheet1.Cells(i, "X").Value = InStr(SrchIn, SrchFor)
because SrchFor has been assigned a value, so the system knows what to search for.
The seconds way fails:
If SrchFor = "abcd" Then
Sheet1.Cells(i, "X").Value = InStr(SrchIn, SrchFor)
as the computer doesn't know what value SrchFor is when it gets to the if, so when it checks to see if it's equal to "abcd", it compares Unknown to abcd and that comparison fails, so it doesn't do the code after the if.
To allow for expansion, and allow it to be able to search for many strings, we need to expand the SrchFor into a list of values (an array), and then search for each item in the array
This would make the function look like this:
Option Explicit
Sub SearchInColumn()
Dim LastRow As Long
Dim i As Long
Dim SrchIn As String
Dim SrchFor(2) As String '2 for 2 strings
Dim SearchForIndex As Long
Dim FoundPos As Long
SrchFor(1) = "abcd"
SrchFor(2) = "NewString"
LastRow = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow
SrchIn = Sheet1.Cells(i, 5).Value
For SearchForIndex = 1 To 2
'use same number as you put into the array definition
FoundPos = InStr(SrchIn, SrchFor(SearchForIndex))
If FoundPos > 0 Then Exit For
Next
Sheet1.Cells(i, "X").Value = FoundPos
Next i
End Sub
This will simply terminate once any of the strings is given, and return the position of that string.
Also, Note the use of Option Explicit - this forces you to define every variable - it was useful when I managed to spell SearchForIndex as SerchForIndex, instead of having strange results occur
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments