Excel VBA function takes 5 sec to write 100 cells using values it pulled from 1 table row - Stack Overflow

admin管理员组

文章数量:1321046

Getting a table like this:

Set tbl = dataSht.ListObjects("ScopeItemDataTable")

Finding the row by ID like this:

rowIndex = 0
    For i = 1 To tbl.ListRows.Count
        If tbl.DataBodyRange(i, idColumnIndex).Value = searchID Then
            rowIndex = i
            Exit For
        End If
    Next i

Assigning cells in another sheet using data from the row like this:

masterSht.Range("E12").Value = tbl.DataBodyRange(rowIndex, 2).Value

There are about 100 of the above assignments in the function call, and the function takes 5 seconds to complete.

Is there a more efficient way to do this that wont take so long?

Only tried what was described above. Still new to using VBA with Excel macros (and VBA in general)

Getting a table like this:

Set tbl = dataSht.ListObjects("ScopeItemDataTable")

Finding the row by ID like this:

rowIndex = 0
    For i = 1 To tbl.ListRows.Count
        If tbl.DataBodyRange(i, idColumnIndex).Value = searchID Then
            rowIndex = i
            Exit For
        End If
    Next i

Assigning cells in another sheet using data from the row like this:

masterSht.Range("E12").Value = tbl.DataBodyRange(rowIndex, 2).Value

There are about 100 of the above assignments in the function call, and the function takes 5 seconds to complete.

Is there a more efficient way to do this that wont take so long?

Only tried what was described above. Still new to using VBA with Excel macros (and VBA in general)

• excel
• vba

Share Improve this question Follow edited Jan 17 at 19:48 BigBen 50.2k77 gold badges2828 silver badges4444 bronze badges asked Jan 17 at 19:43 jjfluidjjfluid 1111 silver badge11 bronze badge 2

• 2 The loop is inefficient. Use Range.Find or read the values into an array. – BigBen Commented Jan 17 at 19:44
• 5 Also, are you recreating the XLOOKUP (or VLOOKUP or INDEX/MATCH) function? – BigBen Commented Jan 17 at 19:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

For example, using Match:

Sub Tester()
    
    Dim tbl As ListObject, searchID, m, rngID As Range
    '...
    '...
    
    Set tbl = dataSht.ListObjects("ScopeItemDataTable")
    Set rngID = tbl.ListColumns("ID").DataBodyRange
    
    searchID = "ABC123"
    
    m = Application.Match(searchID, rngID, 0) 'match is faster than looping...
    If Not IsError(m) Then 'if got a match
        masterSht.Range("E12").Value = tbl.DataBodyRange.Cells(m, 2).Value
    End If

End Sub

本文标签： Excel VBA function takes 5 sec to write 100 cells using values it pulled from 1 table rowStack Overflow