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.
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.
InStr([start, ]string1, string2[, compare])