MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
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...
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
extract zip code or specific digits from text address | 6 | 25 | |
remove extra space at end of cell | 12 | 35 | |
HOW I CAN MANUALLY Format AN COLUMN ! | 2 | 18 | |
Countdown Timer in Excel | 6 | 21 |
Join the community of 500,000 technology professionals and ask your questions.