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]

This is still a loop but try it anyway.

Sub Maybe()
Dim c As Range
Application.ScreenUpdating = False
    For Each c In Range("R6:R" & Cells(Rows.Count, 2).End(xlUp).Row)
        If c Like "*SOFTWARE*" Or c Like "*SW*" Then
            c.Offset(, 5).Value = "Software"
                Else
            c.Offset(, 5).Value = "Hardware"
        End If
    Next c
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

This might be the fastest way you'll ever find!!!!

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

 

[JJCA99]

Jolivanes you win against yourself. Adding the formulas seems to be the fastest. I don't like the formulas in the WS but i'll just add a code to hard code the values in there after.

Thanks for the help!
J.

 

[Norie]

Perhaps.

Dim rng As Range
Dim arrColumnR As Variant
Dim arrColumnW As Variant
Dim idxRow As Long

    With Sheets("Keep")

        Set rng = .Range("R6:R" & .Cells(Rows.Count, 2).End(xlUp).Row)

        arrColumnR = rng.Value
        arrColumnW = rng.Offset(, 5).Value

        For idxRow = LBound(arrColumnR, 1) To UBound(arrColumnR, 1)

            If arrColumnR(idxRow, 1) Like "*SOFTWARE*" Or arrColumnR(idxRow, 1) Like "*SW*" Then
                arrColumnW(idxRow, 1) = "Software"
            Else
                arrColumnW(idxRow, 1) = "Hardware"
            End If

        Next idxRow

        rng.Offset(, 5).Value = arrColumnW

    End With

 

[jolivanes]

Thanks for letting us know.
You don't have to do anything. The formulas are entered and converted to their values. Check the cells. All you'll see is thye values.
Good luck

 

[Peter_SSs]

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

1. Just checking. That isn't your actual code is it?
If so, It must mean that your cells contain strings like "ABC*SOFTWARE*COMPANY", including the *s, otherwise your code would return "HARDWARE" for "ABC SOFTWARE COMPANY" and probably all other cells too.

2. Another check. Are you just looking for the text "SW" or the word "SW"? You did use "word" in your post. I'm asking because all codes so far return "Software" for a cell containing "POSWIN HARDWARE". Is that what you want? Or could that be possible with your data?


BTW, by my testing Norie's is about twice as fast - though for 1700 rows you wouldn't notice the difference.

 

[JJCA99]

Thanks again all - Norie yours works great as well.

Hey Peter,
Good points - The data i'm using only has about 5-6 different variations on describing the support as Hardware or Software so I'm lucky with the data sets I'm using - Currently

I'm all set with this thread, thanks again for all the replies - always good to learn new tricks.

J

 

[jolivanes]

@ Peter_SSs
Re: BTW, by my testing Norie's is about twice as fast
I hope you realize that the "This might be the fastest way you'll ever find!!!!" was not meant seriously. Hence the exclamation marks.

 

[Peter_SSs]

Hey Peter,
Good points - The data i'm using only has about 5-6 different variations on describing the support as Hardware or Software so I'm lucky with the data sets I'm using - Currently

OK, great. It's always a bit tricky when you are guessing what data might be possible.

@ Peter_SSs
Re: BTW, by my testing Norie's is about twice as fast
I hope you realize that the "This might be the fastest way you'll ever find!!!!" was not meant seriously. Hence the exclamation marks.