+ Reply to Thread
Results 1 to 8 of 8

VBA Macro Needs to find Last Instance of in Range

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    VBA Macro Needs to find Last Instance of in Range

    I'm trying to write a macro that will take a range of values from sheet 2 and find each of those values LAST(most bottom-right) appearance in another defined range. Below is what I have at the moment. I want the last instances of the numbers to be highlighted. Let me know if you need any more details. Thanks in advance.

    Please Login or Register  to view this content.
    Last edited by mansions2find; 09-21-2015 at 03:37 PM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: VBA Macro Needs to find Last Instance of in Range

    What is the issue with your code?
    Perhaps just review the loops from the max to 1
    Please Login or Register  to view this content.
    BTW keep in mind to use code tags when posting code
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    Re: VBA Macro Needs to find Last Instance of in Range

    I've only been able to highlight incorrect cells with this code. I tried that line you supplied me PCI, and the results are the same. I believe the issue is when i'm actually trying to select the cell that matches a value and then set the background color.

    Please Login or Register  to view this content.
    When I get through the loops and find the most bottom-right cell match in the range, I want to be able to select that matched cell. I don't think I am doing that correctly.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: VBA Macro Needs to find Last Instance of in Range

    See next code
    Adapt the ranges
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    Re: VBA Macro Needs to find Last Instance of in Range

    Thanks! Works like a charm

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: VBA Macro Needs to find Last Instance of in Range

    You're welcome

  7. #7
    Registered User
    Join Date
    09-21-2015
    Location
    Rochester NY
    MS-Off Ver
    2010
    Posts
    18

    Re: VBA Macro Needs to find Last Instance of in Range

    Hey, Was hoping to clear up a little bit of confusion.... The bold line in the code below i believe is what finds the last instance of an ID. Could somebody explain to me how this IF statement checks for that appropriately?

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: VBA Macro Needs to find Last Instance of in Range

    The loop is scanning all cells
    from the last row to the first one with:
    For i = LastRng.Rows.Count To 1 Step -1
    from the last column to the first one with:
    For j = LastRng.Columns.Count To 1 Step -1

    then if a cell is not Empty and is equal to "cell" and is not in the dictionnary list then
    put the right ColorIndex
    and put the value in the dictionnary list
    Last edited by PCI; 10-04-2015 at 05:14 AM. Reason: Addition info added

+ 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. Replies: 2
    Last Post: 07-22-2015, 09:20 AM
  2. [SOLVED] start from bottom, find first instance of "Page", paste copied range below it...possible?
    By fredderf81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2013, 02:02 PM
  3. [SOLVED] To Find every instance of TEXT copy, repeat until no Find found
    By DadaaP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2013, 02:15 PM
  4. [SOLVED] Find first and last instance of text in column range multiple times
    By ShannonHowell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2012, 11:52 AM
  5. Macro VBA find last instance of a word
    By lindylou2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2009, 05:20 PM
  6. Find first and last instance
    By Gixxer_J_97 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2005, 03:35 PM
  7. Find 2nd instance of a word in a range.
    By Grumpy Grandpa in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-05-2005, 11:35 AM

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