+ Reply to Thread
Results 1 to 6 of 6

VBA Help! Comparing values and putting a 1 or a 2

  1. #1
    Registered User
    Join Date
    04-28-2021
    Location
    charlotte
    MS-Off Ver
    2016
    Posts
    3

    Question VBA Help! Comparing values and putting a 1 or a 2

    I have racked my brain over this for a few hours now and I'm at a loss. I have a list of locations in column A, a list of totals in column B - the totals are a sumifs function based on credit card data - but I'm not sharing that data, so I filled in with fake numbers.

    The idea is there are 2 deposits amounts for each location - each total is a sum of deposits for a specific day. I need to put a 1 or a 2 in the count column based on the the totals. 1 will be for the larger of the two amounts. so for example,

    cells A2:A26 are Charlotte
    cells B2:B26 are totals for Charlotte, but cells A2:A9 is one day and cells A10:A26 is the next day.

    I would put "2" in cells F2:F9 and a "1" in cells F10:F26. I would continue to do the same for the different locations. if a location only has 1 total, it would be a 1. if the type is a refund, it would not have a number. These numbers run a totally separate formula on another page in a larger workbook.

    I have used the exact function in column C to see if the row above matches the location then the total. If the result is FALSE FALSE - I know its a new location and to put a "n" in column D and put the total in column E. I am having trouble either with a formula or VBA to compare the values for the same location in column E. the issue is the row amounts are not consistent. This report is done every Monday, and could have more or less rows for the same location and same day. Rows cannot be deleted, because of the sumifs function that pulls the totals. Deleting rows would also delete the data to pull those totals.

    I have attached an example workbook. I took the VBA I wrote out because all it did was hide and unhide rows and columns with a loop and was not helpful.
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA Help! Comparing values and putting a 1 or a 2

    This code will do the steps if done manually by hand :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    04-28-2021
    Location
    charlotte
    MS-Off Ver
    2016
    Posts
    3

    Re: VBA Help! Comparing values and putting a 1 or a 2

    That worked perfectly in the test workbook!! Thank you so much! The only issue with the real workbook is it deletes my data. the data was originally in columns H:T - row value is different depending on number of transactions in a day. I moved the data to be in columns R:AD so that the other columns could do their work, but now I am getting an application-defined or object-defined error.

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA Help! Comparing values and putting a 1 or a 2

    You are welcome.

    You need to adjust the code according to your actual layout, if you can't adjust it by yourself, you need to upload this actual workbook.

  5. #5
    Registered User
    Join Date
    04-28-2021
    Location
    charlotte
    MS-Off Ver
    2016
    Posts
    3

    Re: VBA Help! Comparing values and putting a 1 or a 2

    I have figured it out! Thank you for your help

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA Help! Comparing values and putting a 1 or a 2

    You are welcome, thanks for marking the thread as solved.

    Regards

+ 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. [SOLVED] Putting values in a catagory
    By deanblew in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-06-2016, 09:42 AM
  2. [SOLVED] Putting a zero in column B for duplicate values and 1 for unique values
    By treeantz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-17-2016, 12:37 AM
  3. Charts putting in zero values
    By davidx in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-16-2013, 05:57 AM
  4. putting rows between different values in first column
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2009, 06:03 PM
  5. Putting decimal values in a cell.
    By Craig in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2006, 01:35 AM
  6. putting values into cells is slow
    By inquirer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2005, 11:06 PM
  7. Counting values and putting it in a different column
    By gaby58 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2005, 04:20 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