Excel VBA - Apply Complex Formula

  • Hi team,


    I am unsure if my problem can be solved using Excel VBA, however I will give it a try.


    I have a really long formula as below.


    My ultimate goal is to make this formula short.


    The formula is written to write Yes if Last 3 digit numbers of Table Number and the Team color is matching.

    (For example, as per the table in Data Tab, Team Color must be Blue if last 3 digit numbers of Table Number is 135. Team Color must be Green if last 3 digit numbers of Table Number is 118.)


    Can any VBA Master help me to achieve below:

    1. I want to use the table in Data Tab, instead of keep making the formula long and big.
    2. I want to apply this formula whenever I update the table in Data Tab (I can add/delete/update numbers and Team Colors in future.)


    Below is my current code, and I am attaching the file for your attention.



    Thanks!

    Files

    • Book1.xlsm

      (31.06 kB, downloaded 89 times, last: )
  • Hi,


    An UDF would probably make things easier ...


    Can you confirm Blue ranges from 135 to 140 whereas all other 4 colors have only one code ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again,


    Just found out about the 23 different possible cases for your 5 colors ...:(


    Question :


    Can both the number of colors and the number of cases (last 3 digits) keep increasing or changing ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re,


    Attached is your Test File


    If your data in the Reference sheet exceeds 50 rows, you would have to adjust your UDF accordindly ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad your problem is fixed ;)


    Thanks for your Thanks AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)