+ Reply to Thread
Results 1 to 3 of 3

VBA code for If same ID then a few functions

  1. #1
    Registered User
    Join Date
    08-01-2019
    Location
    New york
    MS-Off Ver
    2016
    Posts
    1

    VBA code for If same ID then a few functions

    So I am trying to make a macro with data that has IDs starting in column "A2:" and trades starting in column "D2". The IDs are sometimes repeated as you can see below. I want the macro to only populate rows if negative values or both negative and positive values exist in the trade column given the corresponding ID, and to NOT show the row if only positive for the ID. I have bolded the 2 rows that should be deleted in this example to make it easier to see. Also in the expected if possible, I am looking to label which rows were a combination of positive and negative values, or just negatives. So in the expected below in column "O" you will see "RR" on the rows which had positive and negative, and "R" on the IDs contained only negative.
    Please Login or Register  to view this content.
    Below is the code I have so far but it does not work properly and I have not attempted to include the part in Column "O"
    Please Login or Register  to view this content.
    I get an error on
    obj.Add Cells(j, 2).value, Cells(j, 1).value
    It seems as though the error occurs when theres repeated value the error I receive is: "Item has already been added. Key in dictionary: '-1000' Key being added '-1000'

    I am open to any other approach on this but this is currently the progress I have made. Please let me know if you have any other questions regarding the data, any help would be greatly appreciated. This would also serve as a great learning tool for me and I am sure others as well. Thank you

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA code for If same ID then a few functions

    Hello boojangus,

    Welcome to the forum!

    I simplified your macro by using a single dictionary object and an array.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    As column O can be filled just using an easy formula combining REPT & COUNTIFS so after you can apply a filter for rows to delete,
    all needs around 10 codelines … A workbook attachment may be appreciated to bring a more targeted help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dual code help. Unable to come up with a code to perform functions
    By justigger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2015, 06:06 AM
  2. Put Existing Functions in VBA Code
    By wwconslt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2015, 01:57 PM
  3. Using Functions to replace my VBA code
    By bluespire in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-14-2012, 01:08 PM
  4. [SOLVED] Using Formulas/Functions in VBA code
    By mike703 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2012, 01:17 PM
  5. Using Functions in VBA code
    By Chris Gorham in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2005, 08:10 AM
  6. using worksheet functions in code
    By mike allen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-14-2005, 07:05 PM
  7. [SOLVED] not sure what functions/code to use?
    By s&d in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-07-2005, 09:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1