Varsakov
07-12-2013, 12:56 PM
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
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