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