+ Reply to Thread
Results 1 to 4 of 4

Remove range limitation of formula & extend to whole column & possibly execute in VBA?

  1. #1
    Forum Contributor
    Join Date
    01-31-2015
    Location
    East Midlands
    MS-Off Ver
    Excel 2019
    Posts
    132

    Remove range limitation of formula & extend to whole column & possibly execute in VBA?

    Windows 10
    Excel 2019


    I have the below formula which looks at the range E2:E100 and if it finds each cell in the range has the Number 1 it returns the text "M", if it finds each cell in the range has the Number 2 it returns the text "S". If it finds a mix of the numbers 1 and 2 it returns the text "M+S".

    In the E Column there will always be only the Numbers 1 and 2.

    Please Login or Register  to view this content.
    I would like to remove the range limitation of E2:E100 and have the formula look down all of Column E and where it finds unique data in Column B, then look down the whole of Column E and if it finds each cell in the range has the Number 1 it returns the text "M", if it finds each cell in the range has the Number 2 it returns the text "S". If it finds a mix of the numbers 1 and 2 it returns the text "M+S".

    An example would be:

    Please Login or Register  to view this content.
    I also have the VBA code below that looks at Column E and for each unique range in Column E, it alternately colours each range from Column A to Column J.

    I wonder if, as an alternate or better way to run the above formula, it could be incorporated into the below
    function. I am unsure if it is better to do it this way or just use the formula on its own.

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 02-15-2024 at 10:43 PM. Reason: Added code tags for data formatting

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Remove range limitation of formula & extend to whole column & possibly execute in VBA?

    Here are 2 different solutions with a formula suitable for Excel 2019:

    Please try in F2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or try in F2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    01-31-2015
    Location
    East Midlands
    MS-Off Ver
    Excel 2019
    Posts
    132

    Re: Remove range limitation of formula & extend to whole column & possibly execute in VBA?

    Hi Hans

    Thank you for not one, but two perfect solutions.

    I could not have hoped for better help.

    Thank you again.

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Remove range limitation of formula & extend to whole column & possibly execute in VBA?

    You are Welcome!

    Thanks for the feedback and rep .

    Glad to have helped.

+ 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. Extend formula - for a range of columns
    By Wabisch in forum Excel General
    Replies: 2
    Last Post: 05-25-2020, 04:42 PM
  2. How to remove limitation of VLOOKUP
    By Merrysa in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-17-2018, 07:50 AM
  3. Extend formula range dinamically while using an array ?
    By andreapenna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2017, 10:48 AM
  4. A Challenge:Extend Code of a button to remove the Formula of the Saved Page and lock
    By ronettes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2014, 06:01 AM
  5. [SOLVED] Extend current formula for a date range
    By playaller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2013, 06:26 PM
  6. Formula required to return the max figure in a range (possibly more than 1)
    By darrenkaye in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2013, 05:28 AM
  7. How to extend a formula for a whole column?
    By Billznik in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2008, 03:46 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