View Full Version : InStr - Not operating
Crikriek
05-09-2017, 02:25 AM
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
05-09-2017, 03:12 AM
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
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.
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
05-09-2017, 11:47 AM
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
05-10-2017, 04:50 PM
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
05-10-2017, 11:59 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.