Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting 2 Linked Arrays [Retry]
I tried posting this last week and never got a reponse, so I thought I would try again now that the holidays are over:
I'm currently using the code below to create a goto userform in an excel spreadsheet. This works fine if the sheet is sorted by the Job Name, but it may not always be. So, what'd I'd like to do is sort the two arrays. The first by job #, the second by Job name, but still have them be linked together, so that a job can be found by either the # or the name, and the other matching component can be seen. The way it works now, you can enter in the job #, and then see the matching job name, or vice versa, then click okay and be taken to that job in the spreadsheet. I would like to keep that functionality. Just using matching listindexes, as I'm doing now, would not work upon sorting. Thanks for any help. Option Explicit Dim jobnum() Dim jobname() Dim i As Long Dim temppos As Integer Private Sub CancelBut_Click() Unload GotoJobForm End Sub Private Sub cmbJobName_Change() cmbJobNum.ListIndex = cmbJobName.ListIndex End Sub Private Sub cmbJobNum_Change() cmbJobName.ListIndex = cmbJobNum.ListIndex End Sub Private Sub OkayBut_Click() Rows(cmbJobName.ListIndex + 3).EntireRow.Select Unload GotoJobForm End Sub Private Sub UserForm_Initialize() ReDim jobnum(3 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row) ReDim jobname(3 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row) For i = 3 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row jobnum(i) = Rows(i).Columns("A").Value Next For i = 3 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row temppos = InStr(1, Rows(i).Columns("B").Value, Chr(10), vbTextCompare) If temppos < 0 Then jobname(i) = Left(Rows(i).Columns("B").Value, temppos - 1) Else jobname(i) = Rows(i).Columns("B").Value End If Next For i = 3 To ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row cmbJobNum.AddItem jobnum(i) cmbJobName.AddItem jobname(i) Next SendKeys "{tab}" SendKeys "+{tab}" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting non-linked cells in a linked worksheet | Excel Discussion (Misc queries) | |||
Switch To/Retry message appears | Excel Programming | |||
Sorting Arrays | Excel Programming | |||
List boxes, Arrays and Sorting | Excel Programming | |||
VBA: Sorting arrays in hierarchichal data structu Help!!! | Excel Programming |