I am attempting to use a search string that will take priority over some select statements. The Search String statements run correctly, but the Select Statements get skipped over and dont populate.
For Each pCell In Range("C2:C" & Range("B" & Rows.Count).End(3)(1).Row)
If InStr(1, pCell.Value, "TSC") <> 0 Or _
InStr(1, pCell.Value, "MOBILE") <> 0 Or _
InStr(1, pCell.Value, "410WED") <> 0 Then
Select Case Len(pCell)
Case Is = 6
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],3)"
Case Is = 7
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],4)"
Case Is = 8
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],5)"
Case Is = 9
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
Case Is = 10
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)"
Case Is = 11
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
Case Is = 12
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
Case Is = 13
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
Case Is = 14
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],8)"
Case Is = 15
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],9)"
Case Is = 16
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],10)"
Case Is = 17
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
Case Is = 20
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],11)"
Case Is = 21
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],15)"
Case Is = 32
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
Case Is = 42
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
Case Is = 43
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
Case Is = 44
Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],6)"
End Select
End If
Next pCell
LR = Range("B1").End(xlDown).Offset(1, 0).Row
Range("D2:D" & LR).Value = Range("D2:D" & LR).Value
Thanks,
Ray
Bookmarks