CODEWITHSUNDEEP
vbaexcelmodule
Will Cheng
Will Cheng

Reputation: 33

InStr Function Not Working

Essentially I have a loop where on the eighth loop of the program, I want the program to see if a certain cell contains the string "Y". If it contains Y, it should go to the allocated cell and mark "X", if it does not, it should go to the adjacent cell and mark "X". However, no matter how I set up the InStr function, it always return marks the string as not found and marks it in the not found cell. This is regardless of whether or not there is a "Y" Can someone point me in the correct direction?

For i = 1 To 10
    If i = 1 Then
        rng1.Copy Destination:=rng2

    ElseIf i = 2 Then
        rng1.Offset(0, 1).Copy Destination:=rng2.Offset(0, 1)

    ElseIf i = 3 Then
        rng1.Offset(0, 2).Copy Destination:=rng2.Offset(0, 2)

    ElseIf i = 4 Then
        rng1.Offset(0, 3).Copy Destination:=rng2.Offset(0, 3)

    ElseIf i = 5 Then
        rng1.Offset(0, 4).Copy Destination:=rng2.Offset(0, 4)

    ElseIf i = 6 Then
        rng1.Offset(0, 5).Copy Destination:=rng2.Offset(0, 5)

    ElseIf i = 7 Then
        rng1.Offset(0, 6).Copy Destination:=rng2.Offset(0, 6)

    ElseIf i = 8 Then
        If InStr(1, cellText, "Y", 1) > 0 Then  '<~Problem here
            rng2.Offset(0, 7).Value = "X"
        Else
            rng2.Offset(0, 8).Value = "X"
        End If

    ElseIf i = 9 Then
        rng1.Offset(0, 10).Copy Destination:=rng2.Offset(0, 9)

    Else
        rng1.Offset(0, 11).Copy Destination:=rng2.Offset(0, 10)

    End If
    Next i

Upvotes: 0

Views: 958

Answers (1)

Rory
Rory

Reputation: 34045

This isn't directly addressing your main question, but a demonstration of how to use Select Case to shorten your code (and also adds a message box to check your cellText variable):

For i = 1 To 10
    Select Case i
        Case 1 To 7
            rng1.Offset(0, i - 1).Copy Destination:=rng2.Offset(0, i - 1)
        Case 8
            MsgBox cellText
            If InStr(1, cellText, "Y", 1) > 0 Then  '<~Problem here
                rng2.Offset(0, 7).Value = "X"
            Else
                rng2.Offset(0, 8).Value = "X"
            End If

        Case 9 To 10
            rng1.Offset(0, i + 1).Copy Destination:=rng2.Offset(0, i + 1)

    End Select
Next i

Upvotes: 1

Related Questions