VBA multiple list of values for variable


Joined
Feb 14, 2018
Messages
1
Reaction score
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
 
Joined
Mar 7, 2018
Messages
3
Reaction score
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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top