VBA multiple list of values for variable

[Kermy812]

In my script I have:

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:

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 - any ideas?

 

[dmt32]

Hi,
welcome to forum

Just seen this post - have you managed to find a solution for it?


Dave

 

[Kermy812]

Not yet - I'm just keep adding a "if" statement each time, as it seems to grow a little each month. It's just a bit a of a pain to keep editing each time I have a new value to look for. After googling around, I think I need to revamp how I'm doing this, and use Vlookup with a table. It's a bit to get my head around though... If you have suggestions, I'm all ears!

 

[dmt32]

Not yet - I'm just keep adding a "if" statement each time, as it seems to grow a little each month. It's just a bit a of a pain to keep editing each time I have a new value to look for. After googling around, I think I need to revamp how I'm doing this, and use Vlookup with a table. It's a bit to get my head around though... If you have suggestions, I'm all ears!

Hi,
Try this:

Add a sheet to workbook & name it as required or leave default name

Column A - enter all your text strings
Column B – enter related numeric values

In STANDARD module place following code

 Function MySets(ByVal MyStores As Integer, ByVal Text As String) As Variant    
    Dim TextStrings As Variant, m As Variant
    Dim wsTextString As Worksheet
    
    On Error GoTo exitfunction
    Set wsTextString = ThisWorkbook.Worksheets("Sheet1")
    
    With wsTextString
        TextStrings = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value
        m = Application.Match(Text, TextStrings, False)
        If Not IsError(m) Then MySets = Application.RoundUp(MyStores / .Cells(CLng(m), 2).Value, 0) Else Err.Raise 9
    End With
exitfunction:
If Err <> 0 Then MySets = (Error(Err))
End Function

Change the sheet name shown in RED as required

example use

 Sub Test() 
    MsgBox MySets(160, Range("F1").Value)
End Sub

Or you can use as UDF in worksheet

=MySets(160,F1)

Hope Helpful

Dave

 

[Kermy812]

I think I'm doing something wrong - When I "Step Into" I get "Compile Error Expected End Sub" "OK". When you say "In STANDARD module place following code", Am I putting the code in my Macro or doing something else with it?

 

[dmt32]

from VB Editor Insert > Module

paste function to the module.

Providing you have created the worksheet mentioned in my post, the Function should return the required value based on text in Range("F1") of the active sheet and value passed to MyStores argument.

Dave

 

[Kermy812]

Thanks bunch Dave! I got it now. I guess I know enough to be dangerous - I have not done a function before. I will have to try to learn more... I see you're using "match" in the code. Haven't used that either. Before your post, I was considering using index & match too, but was trying to figure it out as well. I did have a little trouble with the getting the return value into my loop so I did this which seems to work:

Dim MySets2 As IntegerMySets2 = MySets(160, Range("F1").Value)


    For x = 1 To MySets2 - 1
        'do some stuff
    
    Next x

Again, Thanks a bunch - I really appreciate it.

 

[Kermy812]

Whoops - Carriage return got away...

Dim MySets2 As Integer
MySets2 = MySets(160, Range("F1").Value)
    For x = 1 To MySets2 - 1
        'do some stuff
    Next x