The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Set wsContracts = ThisWorkbook.Sheets("Contracts")
' Load Contracts array from Contracts sheet
glgContractsColumns = wsContracts.Range("ContractsTable").Columns.Count
ContractsArray() = Range(Cells(3, 1), Cells(1048576, glgContractsColumns).End (xlUp)).Value
liContracts.List = ContractsArray()
liContracts.ListIndex = liContracts.ListCount - 1
Private Sub liContracts_Click()
'On Error Resume Next
With Application
'Populate Contracts form fields from Contracts array
teContractID = .VLookup(liContracts.Value, ContractsArray, 1, False)
teDateCreated = .VLookup(liContracts.Value, ContractsArray, 2, False)
coStatus = .VLookup(liContracts.Value, ContractsArray, 3, False)
coPosition = .VLookup(liContracts.Value, ContractsArray, 4, False)
teAgency = .VLookup(liContracts.Value, ContractsArray, 5, False)
teAgencyPhone = .VLookup(liContracts.Value, ContractsArray, 6, False)
teAgencyFax = .VLookup(liContracts.Value, ContractsArray, 7, False)
teContact = .VLookup(liContracts.Value, ContractsArray, 8, False)
teContactTitle = .VLookup(liContracts.Value, ContractsArray, 9, False)
teContactPhone = .VLookup(liContracts.Value, ContractsArray, 10, False)
teContactMobile = .VLookup(liContracts.Value, ContractsArray, 11, False)
teContactEmail = .VLookup(liContracts.Value, ContractsArray, 12, False)
teClient = .VLookup(liContracts.Value, ContractsArray, 13, False)
teLocation = .VLookup(liContracts.Value, ContractsArray, 14, False)
teLength = .VLookup(liContracts.Value, ContractsArray, 15, False)
teStartDate = .VLookup(liContracts.Value, ContractsArray, 16, False)
teCVSubmitted = .VLookup(liContracts.Value, ContractsArray, 17, False)
coSubmissionMethod = .VLookup(liContracts.Value, ContractsArray, 18, False)
coJobSource = .VLookup(liContracts.Value, ContractsArray, 19, False)
teReference = .VLookup(liContracts.Value, ContractsArray, 20, False)
teKeywords = .VLookup(liContracts.Value, ContractsArray, 21, False)
teRate = .VLookup(liContracts.Value, ContractsArray, 22, False)
coPerUnit = .VLookup(liContracts.Value, ContractsArray, 23, False)
teNotes = .VLookup(liContracts.Value, ContractsArray, 24, False)
teDiary = .VLookup(liContracts.Value, ContractsArray, 25, False)
End With
End Sub
Set wsContracts = ThisWorkbook.Sheets("Contracts")
' Load Contracts array from Contracts sheet
glgContractsColumns = wsContracts.Range("ContractsTable").Columns.Count
' just the first column
ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, 1).End(xlUp)).Value
Dim lContractRow As Long
Private Sub liContracts_Click()
'On Error Resume Next
With Application
lContractRow = .WorksheetFunction.Match(liContracts.Value, ContractsArea, 0)
' now load the whole thing as before
ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, glgContractsColumns).End(xlUp)).Value
liContracts.List = ContractsArray()
liContracts.ListIndex = liContracts.ListCount - 1
'Populate Contracts form fields from Contracts array
' now you know the row, just reference the array directly
teContractID = ContractsArray(lContractRow, 1)
teDateCreated = ContractsArray(lContractRow, 2)
coStatus = ContractsArray(lContractRow, 3)
coPosition = ContractsArray(lContractRow, 4)
.
.
.
.
Private Sub liContracts_Click()
Dim lContractRow As Long
'On Error Resume Next
With Application
ContractsArray() = Range(Cells(3, 1), Cells(1048576, 1).End(xlUp)).Value
lContractRow = .WorksheetFunction.Match(liContracts.Value, ContractsArray, 0)
'now load the whole thing as before
ContractsArray() = Range(Cells(3, 1), Cells(wsContracts.Rows.Count, glgContractsColumns).End(xlUp)).Value
liContracts.List = ContractsArray()
Note that I would recommend using wsContracts.Rows.Count instead of 1048576 - this gives you flexibility and your code will still run on a pre-2007 Excel where the row limit was smaller, or on a future version where the number might go up...
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.