Results 1 to 3 of 3
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Compare and Add Exceptions (VBA/Excel/2000)

    I think I am losing it here <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>!

    I have a long list of names in an array that I need to compare to a list in cells. If it is not in any of the cells, I need to add it at the end. If it is there, I can go on to the next one.

    <pre> For SAS = 1 To KLM <font color=red>'Number of items in Array</font color=red>
    For Delta = DataTop To DataBottom <font color=red>'Where the cells are</font color=red>
    Select Case Left(Cells(Delta, 2), 2)
    Case AbbrevArray(SAS)
    Exit For
    Case xxx <font color=red>'Bogged down here</font color=red>
    <font color=red>'Add to list</font color=red>
    End Select
    Next Delta
    Next SAS</pre>


    This is not right, but my brain is so <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> fried from this project; I cannot think.
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare and Add Exceptions (VBA/Excel/2000)

    You will have to keep track of some things to do this. I added two variables for this.
    <pre> Dim blnFound As Boolean
    Dim lngRow As Long

    ' Initialize row number
    lngRow = DataBottom

    For SAS = 1 To KLM
    ' Initialize signal flag
    blnFound = False

    For Delta = DataTop To DataBottom
    If Left(Cells(Delta, 2), 2) = AbbrevArray(SAS) Then
    ' Set signal flag
    blnFound = True
    Exit For
    End If
    Next Delta

    If blnFound = False Then
    ' Not found? So add to end
    lngRow = lngRow + 1
    Cells(lngRow, 2) = AbbrevArray(SAS)
    End If
    Next SAS</pre>

    I'm not sure that you want to add the AbbrevArray element, but you didn't specify anything else.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Compare and Add Exceptions (VBA/Excel/2000)

    Yes, I am going to add it to the list and at the end, sort everything. Think I may have enough to run with. Thanx!

    Here is the finished solution:<pre> For SAS = 1 To KLM 'Number of items in Array
    blnFound = False

    For Delta = FirstDJobSumm To lastDJobSumm <font color=red>'Where the cells are</font color=red>
    If Left(Cells(Delta, 2), 2) = AbbrevArray(SAS) Then
    <font color=red>' set signal flag</font color=red>
    blnFound = True
    End If
    Next Delta
    If blnFound = False Then
    lngRow = lngRow + 1
    range(Cells(Delta, 2), Cells(Delta, 14)).Select <font color=red>'Select row</font color=red>
    Selection.Insert shift:=xlDown <font color=red> 'Add row</font color=red>
    Cells(Delta, 2) = LongNameArray(SAS) <font color=red>'Add name from array</font color=red>
    lastDJobSumm = lastDJobSumm + 1 <font color=red> 'Increment</font color=red>
    NumbAddits = NumbAddits + 1 <font color=red> 'Used later</font color=red>
    End If
    Next SAS
    <font color=red>'sort the Job Summary</font color=red>
    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
    </pre>

    I just found out that my predecessor did not use Option Explicit when I added it to my module. Everything blew out of the water.
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •