How to implement an InStr search function?

Question

I have a tool that loops through rows of a dataset and display cells contents (job information) if the rest of that row matches criteria I have laid out in column A. Instead of matching .value in this script, how could I implement something like InStr so it doesn't need to match a whole cell case, but could be a variable within a sentence or text string? currently I would want this to incorporate the InStr feature, but I can't figure it out:

   For Each cSkills In rSkills

            If c.Value = cSkills.Value Then i = i + 1

        Next cSkills

rest of script:

Sub Find_1_Skill()


Dim c As Range, r As Range
Dim row As Integer, i As Integer
Dim rSkills As Range, cSkills As Range
Dim JobCodeMatch As Integer, JobTitleMatch As Integer, CLevelMatch As Integer
Dim JobCode As String, JobTitle As String, CareerLevel As String

row = 2
JobCodeMatch = 2
JobTitleMatch = 2
CLevelMatch = 2

Set rSkills = Application.Selection
Set rSkills = Application.InputBox("Select Job Competencies in Column A", TitleID, rSkills.Address, Type:=8)

Do While row < 2400

    i = 0

    JobCode = Cells(row, 6).Value
    JobTitle = Cells(row, 7).Value
    CareerLevel = Cells(row, 14).Value

    Set r = Range(Cells(row, 6), Cells(row, 336))

    For Each c In r

        For Each cSkills In rSkills

            If c.Value = cSkills.Value Then i = i + 1

        Next cSkills

    Next c

    If i = 1 Then

        Cells(JobCodeMatch, 3) = JobCode
        Cells(JobTitleMatch, 4) = JobTitle
        Cells(CLevelMatch, 5) = CareerLevel

        JobCodeMatch = JobCodeMatch + 1
        JobTitleMatch = JobTitleMatch + 1
        CLevelMatch = CLevelMatch + 1

    End If

    row = row + 1

Loop

End Sub

why are you re-inventing? Excel's Find function does support partial match over either value or formula. Just do CTRL+F and record macro.Read that code and you can adopt it as per your need. — cyboashu
– cyboashu, Commented Mar 28, 2019 at 17:46
@cyboashu oh, so this will match any value I put in there? It doesn't have to be explicit in the sense that it only matches where the whole cell = the value? Say the word analytics was in a sentence within a cell, this would still match those? I was under the impression it would only match to a job where the whole cell = analytics — nick lanta
– nick lanta, Commented Mar 28, 2019 at 17:49
Whether Range.Find searches the whole cell, parts of it, formulas, or values, depends on how it's configured - keep in mind that the optional parameters are "remembered" between calls, so it's best to supply them all when invoking it from VBA code, to avoid bad surprises. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 28, 2019 at 17:52

Tom · Accepted Answer · 2019-03-28 19:35:03Z

1

You could implement a Find FindNext method using xlPart matches

Public Sub demo()
    Dim rSkills As Range, cSkills As Range, searchRng As Range
    Dim firstSkill As String
    Dim JobCode As String, JobTitle As String, CareerLevel As String
    Dim c

    With SrcSheet
        Set searchRng = .Range(.Cells(2, 6), .Cells(.Cells(.Rows.Count, 6).End(xlUp).Row, 336))
    End With

    Set rSkills = Application.Selection
    Set rSkills = Application.InputBox("Select Job Competencies in Column A", , rSkills.Address, Type:=8)

    For Each c In rSkills
        Set cSkills = searchRng.Find(c.Value2, lookat:=xlPart)
        If Not cSkills Is Nothing Then
            firstSkill = cSkills.Address

            Do
                With SrcSheet
                    JobCode = .Cells(cSkills.Row, 6).Value2
                    JobTitle = .Cells(cSkills.Row, 7).Value2
                    CareerLevel = .Cells(cSkills.Row, 14).Value2
                End With

                ' Use Ctrl+G to view debug window
                Debug.Print cSkills.Address, cSkills.Value2

                Set cSkills = searchRng.FindNext(cSkills)
            Loop Until cSkills Is Nothing Or firstSkill = cSkills.Address
        End If
    Next c
End Sub

edited Mar 28, 2019 at 19:35

answered Mar 28, 2019 at 18:05

Tom

9,9184 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

Caveat: unqualified Cells is an implicit member call against [_Global], which ultimately resolves to ActiveSheet.Cells - IOW cSkills search result might not be on the same worksheet as the sheet being searched (e.g. if searchRng is set to another sheet than ActiveSheet).

Mathieu Guindon Over a year ago

Oops, I meant the cSkills search result might not be on the same sheet as the sheet JobCode & other values are being pulled from.

Tom Over a year ago

@MathieuGuindon yup agreed it would be best to qualify these ranges explicitly but was a copy paste job from ops code (still doesn’t really excuse it though)

Collectives™ on Stack Overflow

How to implement an InStr search function?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related