VBA Loop Speed - String Search and update

[JJCA99]

Hello all,

I need some help to improve my code. Right now I only have about 1700 lines to go through and the loop i created takes 5-10min. I must be doing something wrong or is there a way to speed up this code?

Code searches one column per cell for specific word (2 options) then updates second column based on string.

'HW V SW


Sheets("Keep").Select
Dim sCellVal As String
HS = Cells(Rows.Count, 2).End(xlUp).Row
For c = 6 To HS
sCellVal = Cells(c, 18).Value


If InStr(sCellVal, "*SOFTWARE*") > 0 Or _
 InStr(sCellVal, "*SW*") > 0 Then
Cells(c, 23) = "SOFTWARE"
Else: Cells(c, 23) = "HARDWARE"
End If


Next c

Appreciate any input.

Thanks,
Justin

 

[jolivanes]

Maybe this would be better.

Sub Maybe_With_Formula()
    With Range("Y2:Y" & Cells(Rows.Count, 18).End(xlUp).Row)
        .Formula = "=IF(COUNT(SEARCH({""SOFTWARE"", "" SOFTWARE"", "" SOFTWARE "",""SW "","" SW "","" SW""},RC[-7])),""Software"",""Hardware"")"
        .Value = .Value
    End With
End Sub

However, if Norie's code fits the bill, you should stick with that as it is the fastest and that is what you're after.