Excel VBA

css navigation by Css3Menu.com

Compare List and Insert if Not Found

My customer has a long list of Job Codes but only a few are used each month. He still wanted the entire list to be displayed. Another process builds the summary I I did not desire to mess with.

This thing skims thru the list and if a code is not found, it makes a row and inserts it. The ߡNumbAdditsߢ is used in another process to determine where to put totals.

 KLM = UBound(AbbrevArray)       'How many Abbrevs
    BOAC = UBound(LongNameArray)    'How many names
    Open DataPath & "JobLists.TXT" For Output As #1     'By Product
        For SAS = 1 To KLM                              'List of Abbrevs & Names
            Print #1, AbbrevArray(SAS) & vbTab & LongNameArray(SAS)
    Close #1
    For SAS = 1 To KLM  'Number of items in Array
        blnFound = False
        For Delta = FirstDJobSumm To lastDJobSumm  'Where the cells are
            If Left(Cells(Delta, 2), 2) = AbbrevArray(SAS) Then
                ' set signal flag
                blnFound = True
            End If
        Next Delta
        If blnFound = False Then
            lngRow = lngRow + 1
       '     Cells(lngRow, 2) = AbbrevArray(SAS)
            range(Cells(Delta, 2), Cells(Delta, 14)).Select
            Selection.Insert shift:=xlDown
            Cells(Delta, 2) = LongNameArray(SAS)
            lastDJobSumm = lastDJobSumm + 1
            NumbAddits = NumbAddits + 1
        End If
    Next SAS
    'sort the Job Summary
    range(Cells(FirstDJobSumm, 2), Cells(lastDJobSumm, 14)).Select
    Selection.Sort Key1:=range(Cells(FirstDJobSumm + 1, 2).Address), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

A special thanks to the WOPR VB/VBA Lounge and especially HansV for the nucleus of this solution.

© 2004-2017

Updated:  06/14/2017 17:53
This page added:  10 March 2004