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)
Share Improve this question edited Jan 17 at 19:48 BigBen 50.2k7 gold badges28 silver badges44 bronze badges asked Jan 17 at 19:43 jjfluidjjfluid 111 silver badge1 bronze badge 2 |1 Answer
Reset to default 2For 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
Range.Find
or read the values into an array. – BigBen Commented Jan 17 at 19:44XLOOKUP
(orVLOOKUP
orINDEX/MATCH
) function? – BigBen Commented Jan 17 at 19:45