VBA code to select a cell when specific letters appears and sort

I need some help to understand and modify some code I found in this answer:

Sub Main()

    Dim cell As Range
    Dim nextRow As Long
    For Each cell In Range("K50:K200")
        If StrComp(cell, "Late", vbTextCompare) = 0 Then
            nextRow = Range("J" & Rows.Count).End(xlUp).Row + 1
            Range("J" & nextRow) = Range("B" & cell.Row)
            Range("K" & nextRow) = Range("C" & cell.Row)
            Range("N" & nextRow) = Range("G" & cell.Row)
            Range("O" & nextRow) = Range("H" & cell.Row)
        End If
    Next

End Sub

What I'd like is:

  • If late is along with some other words, say late submit, copy values in B and paste in J
  • if row K has progress along with say in progress, copy values in C to K
  • If sick is with say on sick leave, copy values in G to N

I tried so hard, but I still didn't succeed in modifying this code to suit my needs.

Answers


If late is along with some other words, say late submit

In that case, instead of StrComp, use Instr

If Instr(1, cell, "Late", vbTextCompare) > 0 Then

Similarly for the rest.

If you check Excel's help you will notice that Instr returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Syntax

InStr([start, ]string1, string2[, compare])


Need Your Help

Long to Wide Data - Every Nth row

r reshape2 tidyr rvest

I am writing up a new script where I pull (with rvest) an html table from a website. The data is a consistent format, but the values changes daily.

Spring roo: Showing entity names in Arabic

spring internationalization spring-roo

I used this addon to add the Arabic language to my project. It worked fine but I still need to show the entity names in Arabic as well.