Excel VBA Code Does Not Work With If Statement

Question

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

@MitchWheat - Does not work means - prints nothing in the rows in Column X. But when I run it w/o the if-statement, using the commented out line, it does print the position of the string if found, or 0 if the string is not found. — Sandy, Nov 17 '13 at 00:57
@MichaelLiu - I am new to VBA...I have declared SrchFor as a String and it's assigned to a text string to search in Column E, so what should I initialize it to? — Sandy, Nov 17 '13 at 01:04

score 0 · Answer 1 · answered Nov 17 '13 at 00:55

0

SrchFor has not been assigned a value.

answered Nov 17 '13 at 00:55

Gary's Student

95,722
10
59
99

please see my updated question above and please tell me what value needs to be assigned to SrchFor? – Sandy Nov 17 '13 at 02:11

Juan · Answer 2 · 2013-11-17T01:03:31.823

0

This line

LastRow = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row

Always return 1 so the for never execute

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 4
'In the next line you search for the value, but then in the next you use an if to check if             'SrchFor has something but the variable with the value is SrchIn not SrchFor and this point SrchFor 'has nothing. Thats why the if don't work
SrchIn = Sheet1.Cells(i, 5).Value
'SrchFor = "abcd"
If SrchFor = "abcd" Then
Sheet1.Cells(i, "X").Value = InStr(SrchIn, SrchFor)
End If
Next i

edited Nov 17 '13 at 01:03

answered Nov 17 '13 at 00:58

Juan

1,352
13
20

please see my updated question above. The for executes when I do not use the if statement. FYI, I am actually searching SrchFor in SrchIn by using the Instr VBA function - http://www.techonthenet.com/excel/formulas/instr.php – Sandy Nov 17 '13 at 02:18

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

Your code is not working because the line If SrchFor = "abcd" Then will always evaluate to FALSE as SrchFor will always be 0. Few Tips

When working with row variables, it's good to declare them as Long else you might encounter an OverFlow Error.
Always fully qualify your object. In the above code Rows.Count is not fully qualified. You might want to see THIS
Your code can be written as

CODE A

Sub SearchInColumn()
    Dim LastRow As Long, i As Long
    Dim SrchIn As String, SrchFor As String
    Dim ws As Worksheet

    Set ws = [Sheet1]

    SrchFor = "abcd"

    With ws
        LastRow = .Range("E" & .Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            SrchIn = .Cells(i, 5).Value
            If InStr(1, SrchIn, SrchFor) Then
                .Cells(i, "X").Value = InStr(1, SrchIn, SrchFor)
            End If
        Next i
    End With
End Sub

CODE B

Alternatively you could use .FIND/.FINDNEXT instead of looping with LookAt:= as xlPart

Also here is NON VBA SOLUTION

Paste this formula in Cell X1 and pull it down.

=IF(ISERROR(SEARCH("abcd",E1,1)),"",SEARCH("abcd",E1,1))

edited May 23 '17 at 11:50

Community

1
1

answered Nov 17 '13 at 08:20

Siddharth Rout

147,039
17
206
250

Hi Sid, I am going thru your detailed response. So a quick question if you will - if I want to find more than one string like `"abcd"` or `"ef"` or `"xyz"` for `SrchFor` in the function `InStr(SrchIn, SrchFor)`, what is the simplest solution? I thought I could simply use `srchFor` as a variable in the function and use `if SrchFor = "abcd" then do this, else if SrchFor = "ef" then do that, else if SrchFor = "xyz" then do that, end if` – Sandy Nov 17 '13 at 16:50
`If InStr(1, SrchIn, SrchFor1) or InStr(1, SrchIn, SrchFor2) or InStr(1, SrchIn, SrchFor3) .... Then` – Siddharth Rout Nov 17 '13 at 16:51
Yes that would work for a common `Then`. I did a `if-then, else-if-then...end if` as I needed the respective `Then's`. I guess there's not a more 'elegant' way to do this ??... – Sandy Nov 17 '13 at 17:22
Perhaps a select case? – Siddharth Rout Nov 17 '13 at 17:24
had tried that, but let me again with the light that you have shed! – Sandy Nov 17 '13 at 17:26
Hi Sid, as I think about it again, it really doesn't make sense - I can do `InStr(SrchIn, "abcd")`, I can also do `SrchFor = "abcd", InStr(SrchIn, SrchFor)`, but I cannot do `If SrchFor = "abcd" Then, InStr(SrchIn, SrchFor), End If`. So `SrchFor` works as a variable in the 2nd case but not in the 3rd case ???!!! – Sandy Nov 17 '13 at 18:17
Let's start from scratch :) What exactly are you trying to do? earlier you were searching for one word and now in comments you mentioned htat you may have more words? If you can exactly explain then maybe I can give you a direct answer? – Siddharth Rout Nov 17 '13 at 18:27
okay starting from scratch - the reason I wanted an 'elegant' VBA as opposed to a formula is - I wanted to have the flexibility of adding, removing, editing more strings to search over time, so that is why I wanted to treat `SrchFor` as a variable in an if-statement. So the goal of this thread was really how to make `SrchFor` work as a variable in the If statement - thinking if that worked for one word, then adding more words to the variable should not be a problem thereafter... – Sandy Nov 17 '13 at 19:06
Of course, now I am able to do what I needed to do - the only thing bugging me is why `SrchFor` cannot be used as a variable in a if-statement -:) – Sandy Nov 17 '13 at 22:02

score 0 · Accepted Answer · answered Nov 18 '13 at 16:36

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

Ok I think I now understand - in VBA I cannot assign hard values to variables in `if` statements the way I thought I could. It actually has a procedure as you have described. Something new I learned today:-) — Sandy, Nov 20 '13 at 17:28
With this realization and in the interest of keeping things simple and compact, I will now examine why Sid's compact CODEA doesn't work, although his other solution `If InStr(1, SrchIn, SrchFor1) or InStr(1, SrchIn, SrchFor2) or InStr(1, SrchIn, SrchFor3) .... Then` is really a simple solution for a beginner.... — Sandy, Nov 20 '13 at 17:43

Excel VBA Code Does Not Work With If Statement

4 Answers4