VBA Loop Speed - String Search and update

JJCA99

Board Regular
Joined
Dec 4, 2014
Messages
50
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.

Code:
'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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This is still a loop but try it anyway.
Code:
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
 
Upvote 0
This might be the fastest way you'll ever find!!!!
Code:
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
 
Upvote 0
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.
 
Upvote 0
Perhaps.
Code:
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
 
Upvote 0
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
 
Upvote 0
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.

Code:
'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. :)
 
Last edited:
Upvote 0
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
 
Upvote 0
@ 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.
 
Upvote 0
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.
:biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top