+ Reply to Thread
Results 1 to 4 of 4

Multiple cases Macro

  1. #1
    Registered User
    Join Date
    08-14-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Multiple cases Macro

    Hi Everyone,

    Glad to be part of the guru's forum. I have a task on which I'm struggling on and wasn't able to find any solution on that. I want to populate one specific cell, based on the value of two other cells that show the region and the FY. I can do that with four different IF statements but it doesn't work when I try to do it with macros. So, let's say we have a drop down for Regions, a dropdown for Fiscal Years and for each and every combo, there is distinct value with which I want to populate one specific cell. Therefore, everytime the user changes either the Region or the FY the value that will be returned, will change.

    The IF functions I have and work individually are:


    =IF($F$2="EMEAR",IF($J$2="FY17",Dropdown!D31/1000,IF($F$2="EMEAR",IF($J$2="FY18",Dropdown!E31/1000,IF($F$2="EMEAR",IF($J$2="FY19",Dropdown!F31/1000,))))))

    =IF($F$2="AMERICAS",IF($J$2="FY17",Dropdown!D30/1000,IF($F$2="AMERICAS",IF($J$2="FY18",Dropdown!E30/1000,IF($F$2="AMERICAS",IF($J$2="FY19",Dropdown!F30/1000,))))))

    =IF($F$2="APJ",IF($J$2="FY17",Dropdown!D32/1000,IF(F2="APJ",IF($J$2="FY18",Dropdown!E32/1000,IF($F$2="APJ",IF($J$2="FY19",Dropdown!F32/1000,))))))

    =IF($F$2="GRTR CHINA",IF($J$2="FY17",Dropdown!D33/1000,IF($F$2="GRTR CHINA",IF($J$2="FY18",Dropdown!E33/1000,IF($F$2="GRTR CHINA",IF($J$2="FY19",Dropdown!F33/1000,))))))


    I tried to create 4 different macros and another one which will run all the other four, and assigned the latter to a button which when clicked will run them all. Although it worked in separate cells, I can't fine a way when I click the button to populate the returned value to ONE specific button. Like, replacing the value according to the criteria met.

    Hope it makes sense, and I'd appreciate any help .

    Regards,
    H

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple cases Macro

    It would help a lot if you could attach a sample workbook without sensitive data. It looks like this issue could be solved with a combination of MATCH and Index but it depends on how the source data are organized.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-14-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: Multiple cases Macro

    Hi dflak,

    It was resolved with index and match indeed. It's more like a two way lookup

  4. #4
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Multiple cases Macro

    I am confused Are you trying to change the text of a button? If so, then here is the code to do that
    Please Login or Register  to view this content.
    Else it would be better to upload a sample so that I can be clear about your requirements.

+ 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. Vba if then statement with multiple cases and wildcards
    By LightKerosene in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-28-2015, 12:17 PM
  2. [SOLVED] Building multiple Select Cases
    By Tortus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2015, 08:43 AM
  3. Macro for copying data from one sheet to another under different cases?
    By zoenightshade in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-07-2015, 06:23 PM
  4. Add cases opened and closed for each month and specifics about the cases.
    By ChrisLaRoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2014, 08:19 PM
  5. [SOLVED] Identifying part of a string with multiple cases
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-27-2013, 07:32 PM
  6. Split data into multiple sheets - dispatching cases
    By badaza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-20-2012, 11:22 AM
  7. Coding Multiple Cases in VBA
    By billofsoo in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-27-2009, 05:09 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