Using Index Match, adding to the bottom of the row those that are missing

[Varsakov]

Hi All,

Having some trouble with a macro. Basically, I am trying to create a program that will look for the CUSIPS that are in Range A in Range B, and if they don't exist in Range B, insert them at the end of Range B. The way I have this setup in excel is this:

My C column has a list of CUSIPS. In column J, I would like for the User to be able to add a list of cusips and then have the ones that don't already exist in the list of CUSIPS in column C be added at the bottom of the range in C. I have written a macro now that uses the index match, but for some reason it adds all of the cusips that exist in J range to column C, not just the ones that are missing. Can someone take a look and see what I am doing wrong? I can provide a template if necessary. Below is the code that I am using.

Sub Filler()
Dim Row As Variant
Dim NumberOfRows As Long
Row = 0
Dim Cell As Variant
Dim CUSIP_COLUMN As Variant
Dim Msg As Variant




Do While Sheets("Regan Inv").Range("J3").Offset(Row, 0) <> ""


Cell = Range("j3").Offset(Row, 0).Value
NumberOfRows = Worksheets("Regan inv").Range("CUSIPS").Cells.SpecialCells(xlCellTypeConstants).Count
If IsError(Application.Index("CUSIPS", Application.Match(Cell, "CUSIPS", 0))) Then
Cells(NumberOfRows, 3).Offset(2, 0) = Cell
End If
Row = Row + 1

Loop
End Sub


Thank you in advance for your help!

Vasilis

[mancubus]

several methods can be used.

below procedure looks for col A cell values in col B and if not found writes to the first blank cell in col B.

[VBA]
Sub AddMissingValues()

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Application.CountIf(Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row), Range("A" & i).Value) = 0 Then
Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value
End If
Next

End Sub
[/VBA]