InStr - Not operating

[Crikriek]

Hi,

I'm running a script with the InStr function to find a string within a text in a specific cell. Let's that I want the code to enter the values "Nice" in M and "Cool" in N only if there is the string "Top" (not matter the upper cases) in K. I also included an error handler in case the cell is blank or any specific thing not managed by the InStr function.

Problem is that when I run the code it doesn't do anything at all. Could you please help me with that ?

My code is as follows :

Option Compare as Text
Private Sub GoButton_Click()
Dim LastRow As Long
Dim Row As Long
Row = 2
LastRow = wkb.Sheets("Working Data").Cells(Sheets("Working Data").Rows.Count, "A").End(xlUp).Row
On Error GoTo Handler
Do While Row <= LastRow
   If InStr(wkb.Sheets("Working Data").Cells(Row, "K"), "Top") <> 0 Then
        wkb.Sheets("Working Data").Cells(Row, "M") = "Nice"
        wkb.Sheets("Working Data").Cells(Row, "N") = "Cool"
   End If
   Row = Row + 1
Handler : Row = Row + 1
Loop
End Sub

Thanks a lot in advance !

Kr,

Christophe

[mdmackillop]

Using Instr

Option Explicit
Option Compare Text
Private Sub GoButton_Click()
    Dim LastRow As Long
    Dim wkb As Workbook
    Dim Row As Long
    
    Set wkb = ThisWorkbook
    
    Row = 2
    LastRow = wkb.Sheets("Working Data").Cells(Sheets("Working Data").Rows.Count, "A").End(xlUp).Row
    On Error GoTo Handler
    Do While Row <= LastRow
        If InStr(wkb.Sheets("Working Data").Cells(Row, "K"), "Top") <> 0 Then
            wkb.Sheets("Working Data").Cells(Row, "M") = "Nice"
            wkb.Sheets("Working Data").Cells(Row, "N") = "Cool"
        End If
        Row = Row + 1
Handler:          Row = Row + 1
    Loop
End Sub

Better with Find

Sub test()
Dim firstAddress As String
Dim c As Range
With Worksheets("Working Data").Range("K:K")
    Set c = .Find("top", LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Offset(, 2).Resize(, 2) = Array("Nice", "Cool")
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

[rlv]

Problem is that when I run the code it doesn't do anything at all.

it works fine when I run it, however because you are incrementing your loop variable twice on each pass

       
 Row = Row + 1 
 Handler : Row = Row + 1

you are only seeing even-numbered rows, and any occurrence of "Top" in an odd-numbered row is not seen.

[SamT]

Your original Code

Row = 2 
    LastRow = Blah Blah .End(xlUp).Row '<-- Row = 2.    What is .End(xlUp).2?

Yet another way

Option Explicit 
Option Compare Text 

Private Sub GoButton_Click() 
    Dim Rw As Long 
     
    On Error GoTo Handler 

     With Sheets("Working Data")
    For Rw = 2 to .Cells(Rows.Count, "A").End(xlUp).Row 
        If InStr(LCase(.Cells(Rw, "K")), "top") <> 0 Then _
            .Cells(Rw, "M").Resize(, 2) = Array("Nice", "Cool")
       Handler:
    Next
End With 
End Sub

[Paul_Hossler]

Yet another way

Option Explicit
Option Compare Text '   <<<<< Not Compare AS Text

Private Sub GoButton_Click()
    Dim c As Range, r As Range
    
    With Sheets("Working Data")
        Set r = Range(.Cells(1, "K"), .Cells(.Rows.Count, "K").End(xlUp))
    End With
    
    For Each c In r.Cells
        With c
            If InStr(.Value, "Top") <> 0 Then
                .Offset(0, 2).Value = "Nice"
                .Offset(0, 3).Value = "Cool"
            End If
        End With
    Next
    
End Sub

[Leith Ross]

Hello Christophe,

Rather than set the entire Module to ignore case when comparing text, you can choose how InStr will compare the data by setting the fourth argument Compare. This argument is optional. The default setting is Binary.

The InStr function will return either a 0 (zero) or a positive integer. Error handling is not needed because InStr will never throw an error if the string being searched is empty.

If the button is on the worksheet "Working Data", you can use this macro.

Private Sub GoButton_Click()


    Dim Row As Long
        
        With GoButton.Parent
            For Row = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
                If InStr(1, .Cells(Row, "K"), "Top", vbTextCompare) Then
                    .Cells(Row, "M").Resize(1, 2) = Array("Nice", "Cool")
                End If
            Next Row
        End With
    
End Sub

If the button is not on the worksheet "Working Data" then you should this macro.

Private Sub GoButton_Click()


    Dim Row As Long
        
        With Worksheets("Working Data")
            For Row = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
                If InStr(1, .Cells(Row, "K"), "Top", vbTextCompare) Then
                    .Cells(Row, "M").Resize(1, 2) = Array("Nice", "Cool")
                End If
            Next Row
        End With
    
End Sub

[mdmackillop]

A filter solution

Sub TestFilter()
    Dim r As Range
    With Worksheets("Working Data")
        Set r = Range(.Cells(1, 1), .Cells(Rows.Count, "A").End(xlUp)).Offset(, 10)
    End With
    
    r.AutoFilter Field:=1, Criteria1:="=*top*"
    Set r = r.Offset(1).Resize(r.Count - 1)
    r.Offset(, 2).Resize(, 2) = Array("Nice", "Cool")
    r.AutoFilter
End Sub