VBA multiple list of values for variable

Discussion in 'Excel' started by kermy812, Feb 14, 2018.

  1. kermy812

    kermy812

    Joined:
    Feb 14, 2018
    Messages:
    1
    Likes Received:
    0
    In my script I have:

    Code:
    MyStores = 160
    
    Range("F1").Select
            If InStr(1, (Range("F1").Value), "aaa_") Then
            MySets = Application.RoundUp(MyStores / 8, 0)
            End If
      
        Range("F1").Select
            If InStr(1, (Range("F1").Value), "bbb_") Then
            MySets = Application.RoundUp(MyStores / 8, 0)
            End If
      
        Range("F1").Select
            If InStr(1, (Range("F1").Value), "ccc_") Then
            MySets = Application.RoundUp(MyStores / 6, 0)
            End If
      
        Range("F1").Select
            If InStr(1, (Range("F1").Value), "ddd_") Then
            MySets = Application.RoundUp(MyStores / 4, 0)
            End If
      
        Range("F1").Select
            If InStr(1, (Range("F1").Value), "eee_") Then
            MySets = Application.RoundUp(MyStores / 4, 0)
            End If
    My script is looking at cel F1 and if it contains the text "aaa_", it divides 160 (MyStores) by 8 and rounds it up to the nearest whole number.

    But if it contains bbb_ also divide by 8
    if ccc_ then divide by 6
    if ddd_ the divide by 4
    if eee_ then divide by 4 also

    I have about 60 or so text strings I'm seaching for and about a dozen different numbers to divide by, but Is there a better way to do this?
    I'm thinking there should be a better way than 60 "if" statements.
    Is there a way to maybe to have a list of values the its' looking for as a variable, then based on text it found, divide by the corresponding value?
    I'm thinking I would need to do doething like this:

    Code:
      Range("F1").Select
            If InStr(1, (Range("F1").Value), MyString) Then
            MySets = Application.RoundUp(MyStores / MyQty, 0)
            End If
    But I'm not sure how to setup variables MyString or MyQty
     
    kermy812, Feb 14, 2018
    #1
    1. Advertisements

  2. kermy812

    jerome drean

    Joined:
    Mar 7, 2018
    Messages:
    3
    Likes Received:
    0
    you should construct a dictionary of key value where the key is the string and the value the nunber by which you divide and then just go through the dictionary once
     
    jerome drean, Mar 7, 2018
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.
Loading...