Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA Loop Speed - String Search and update
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Loop Speed - String Search and update

    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

  2. #2
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  3. #3
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  4. #4
    New Member
    Join Date
    Dec 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,156
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    Quote Originally Posted by JJCA99 View Post
    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 by Peter_SSs; Sep 20th, 2019 at 12:50 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    Dec 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    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

  9. #9
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    @ 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.
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA Loop Speed - String Search and update

    Quote Originally Posted by JJCA99 View Post
    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.


    Quote Originally Posted by jolivanes View Post
    @ 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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •