Excel vba alternatives to "find" to reduce runtime

vba ide
vba editor alternative
excel alternative
alternatives to excel for large data sets
vba replacement
vba javascript
is vba outdated
is vba hard to learn

The code is supposed to find strings in column A of a worksheet , if cde is found, copy and paste it into the "new" worksheet, if cde not found, continue to find efg and if efg found, copy and paste into the "new" worksheet.

May i ask for advices or opinions on how i can use functions other than .find to reduce the runtime?

Dim LongRow As Long
LongRow = wks.Range("A" & wks.Rows.Count).End(xlUp).Row + 1

'Find efg if not hij if not klm
Set c = wksData.Columns("A:A").Find("efg ", LookIn:=xlValues)

If Not c Is Nothing Then
    wks.Cells(LongRow, 9).Value = c.Value
Else
    Set f = wksData.Columns("A:A").Find("hij", LookIn:=xlValues)
    If Not f Is Nothing Then
        wks.Cells(LongRow, 9).Value = f.Value
    Else
        Set g = wksData.Columns("A:A").Find("klm", LookIn:=xlValues)
        If Not g Is Nothing Then
            wks.Cells(LongRow, 9).Value = g.Value
        End If
    End If
End If         

There are a few ways that you could speed this up. A simple one might be to create a custom sort order for your items, sort your data in that order, and then simply take the first row of data.

Skeleton code would look a bit like this:

Const FIRST_DATA_ROW As Long = 2

Dim wksData As Worksheet, wks As Worksheet
Dim tempRng As Range, sortRng As Range
Dim searchItems As Variant, item As Variant
Dim lastCol As Long, lastRow As Long, rowCount As Long, n As Long
Dim output() As Long
Dim found As Boolean

'HOUSEKEEPING FIRST.
'-------------------
'Define your sheets as required.
Set wksData = ThisWorkbook.Worksheets("Sheet1")
Set wks = ThisWorkbook.Worksheets("Sheet2")

'Define data limits.
With wksData
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Create a temp column to store original sort order.
rowCount = lastRow - FIRST_DATA_ROW + 1
ReDim output(1 To rowCount, 1 To 1)
For n = 1 To rowCount
    output(n, 1) = n
Next
Set tempRng = wksData.Cells(FIRST_DATA_ROW, lastCol + 1).Resize(rowCount)
tempRng.Value = output

'SORT THE DATA.
'--------------
'Define the search items in order.
searchItems = Array( _
    "efg", _
    "hij", _
    "klm")

Application.AddCustomList searchItems

'Execute the sort.
With wksData
    Set sortRng = .Range( _
        .Cells(FIRST_DATA_ROW, "A"), _
        .Cells(lastRow, lastCol + 1))
End With

With wksData.Sort
    With .SortFields
        .Clear
        .Add _
            Key:=sortRng.Columns("A"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=Join(searchItems, ","), _
            DataOption:=xlSortNormal
    End With
    .SetRange sortRng
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'DATA TRANSFER
'-------------

'Check if first item is in the search list.
For Each item In searchItems
    If item = wksData.Cells(FIRST_DATA_ROW, "A").Value2 Then
        found = True
        Exit For
    End If
Next

If Not found Then
    'Nothing is found.
    MsgBox "No items found."
Else
    'Copy data to new sheet.
    With wks
        lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
        .Cells(lastRow + 1, "I").Value = wksData.Cells(FIRST_DATA_ROW, "A").Value2
    End With
End If

'CLEAN UP
'--------

'Unsort data.
With wksData.Sort
    With .SortFields
        .Clear
        .Add _
            Key:=tempRng, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
    End With
    .SetRange sortRng
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

'Clear the temp sort order.
tempRng.ClearContents

Another way would be to log each time you hit a match and just look for higher priority items. Once you've reached a top priority item, then end the search. Again, skeleton code could be like this:

Const FIRST_DATA_ROW As Long = 2

Dim wksData As Worksheet, wks As Worksheet
Dim searchItems As Variant, data As Variant
Dim priority As Long, foundIndex As Long, i As Long, j As Long

'Define your sheets as required.
Set wksData = ThisWorkbook.Worksheets("Sheet1")
Set wks = ThisWorkbook.Worksheets("Sheet2")

'Define the search items in order.
searchItems = Array( _
    "efg", _
    "hij", _
    "klm")

'Read the data into an array.
With wksData
    data = .Range( _
                .Cells(FIRST_DATA_ROW, "A"), _
                .Cells(.Rows.Count, "A").End(xlUp)) _
            .Value2
End With

'Loop through the data.
priority = -1
foundIndex = -1
For i = 1 To UBound(data, 1)
    'Look for a match.
    For j = 0 To UBound(searchItems)
        If data(i, 1) = searchItems(j) Then
            'We have a match, so ...
            'Store the values.
            foundIndex = i
            priority = j
            'Remove lower priority search items.
            If priority > 0 Then ReDim Preserve searchItems(j - 1)
            Exit For
        End If
    Next
    'Stop if we have a top priority match.
    If priority = 0 Then Exit For
Next

'Copy the data.
If foundIndex = -1 Then
    'Nothing is found.
    MsgBox "No items found."
Else
    'Copy data to new sheet.
    With wks
        .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Value = data(foundIndex, 1)
    End With
End If

Alternatives to VBA : excel, Google Sheets would be the closest alternative. However, VBA is basically the ' Swiss-Army knife' of programming. There are countless use-cases for it! Excel 2013 VBA alternative to using Activate and Select. Ask Question Asked 3 years, 11 months ago. Active 2 years, 10 months ago. Viewed 2k times 1. So i took a look


Here is a short code that will search the column only 1x for all your searched sub-strings and should be faster, Try ...

    Sub SearchColumn()

    Dim ws1 As Worksheet, ws2 As Worksheet

    Dim ws1Row As Integer, ws2Row As Integer
    Dim ws1Col As Integer, ws2Col As Integer
    Dim ws1RowMax As Integer

    Dim searchedText(2) As String
    Dim cellText As String

    searchedText(0) = "efg"
    searchedText(1) = "hij"
    searchedText(2) = "klm"

    Set ws1 = Worksheets(1)
    Set ws2 = Worksheets(2)

    ws1Row = 1
    ws1Col = 1 '-- col A, 2 = col B, etc.

    ws2Row = 1
    ws2Col = 1 '-- col A, 2 = col B, etc.

    ws1RowMax = 100 '-- choose a number (can be max - 65000
                    ' or program for last cell.
                    ' There is a protection in the For loop -
                    '  - if a cell is empty, routine will quit

    With ws1
        For ws1Row = 1 To ws1RowMax
            cellText = .Cells(ws1Row, ws1Col)
            If (cellText = "") Then Exit For
            check = CheckMatch(cellText, searchedText)
            If (check) Then
                ws2.Cells(ws2Row, ws2Col) = cellText
                ws2Row = ws2Row + 1
            End If
            ws1Row = ws1Row + 1
            If (ws1Row > ws1RowMax) Then Exit For
        Next ws1Row
    End With

End Sub

Function CheckMatch(textToCheck As String, searchedText() As String) As Boolean
    Dim result As Boolean
    For Each txt In searchedText
        If (InStr(textToCheck, txt)) Then
            result = True
            Exit For
        End If
    Next
    CheckMatch = result
End Function

For this to run as is, make sure you have at least 2 sheets in your workbook. In 1st sheet place your text data in column 1 - or change the number of 'ws1Col' to the number of the column where your text is. You may also need to adjust the starting row in the For loop from 1 to any number where your text search starts. Hope this helps.

What is an alternative to Excel VBA?, Many companies are still managed with Excel spreadsheets. VBA or Visual Basic for Applications is a programming language that allows for� excel vba. share | improve this question | follow | However, I think the main idea was an alternative way to change multiple cells and not to use For Each for that.


For exact match, Match is very fast, but for 3 items you're not going to notice the difference

Dim m, v

For Each v in Array("efg","hij","klm")
    m = Application.Match(v, wksData.Columns("A:A"), 0)
    If Not IsError(m) Then
        wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1, 0).Value = _
                    wksData.Cells(m, 1).Value 
        Exit For
    End If
Next v

If you're not looking for exact match you can use

m = Application.Match(v & "*", wksData.Columns("A:A"), 0)       'look for "begins with v"
m = Application.Match("*" & v & "*", wksData.Columns("A:A"), 0) 'look for "contains v"

Excel Is Obsolete. Here Are the Top 2 Alternatives from R and , The Excel VBA community is absolutely one of the most generous and ( although much easier than Microsoft alternatives) and working with . If you have any doubts regarding this article or any other Excel/VBA related articles, ask in the comments section below. Download the working file below: Related Articles: Using Loop in VBA in Microsoft Excel | The loops in VBA enable us to do a similar task over and over without repetition of code. There are 3 types of loops in Excel VBA.


Will VBA Die in 2019? — The Spreadsheet Guru, Learn why Microsoft Excel VBA automation with macros may be putting your business at risk and why robotic process automation (RPA) may� Things to Remember about Excel Alternatives to VLOOKUP. VLOOKUP can only work from left to right. MATCH function will return the row number. INDEX + MATCH and LOOKUP functions do not require column number, unlike VLOOKUP, require column number to fetch the data even though the required column is already selected.


Excel Automation: RPA is an Alternative to Excel Macros , Hello Excel Gurus, I am wondering if there's any alternative to Excel VBA? The reason why I'm asking this is that I have close to 100 workbooks� VBA is a computer language among many others. The advantage I see with VBA is that it connects well to the Excel object library (Workbook, Worksheet, Range objects) and Access object library (Forms, Reports, Controls objects). However, if you need to slice and dice data consider going open-source: Python, PHP, and R are great examples.


Any alternative to Excel VBA, Apps Script: Just like Excel has a VBA (Visual Basic for Applications), Google Apps Script allows additional programming based on Javascript. You can run� 1. Google Docs Spreadsheet Chances are you already use a lot of Google products on a daily basis (e.g., Gmail, Google+, 2. OpenOffice OpenOffice is a solution that is being utilized by more and more businesses these days. It’s yet another 3. Excel Web Application Strangely, one of the best