VBA to compare cell value against multiple values stored in a variable

[K_Lucky]

Hello,

I am still learning VBA and previously had some code that compared a cells value against a variable that I had multiple different values stored in. In a string of unfortunate events this code has been lost.
I have been trying to recreate what I had but am hitting a road block. I believe I previously had the variable as a Boolean which I am trying to recreate.
Below is the code I have started with but it clearly is not working. If anyone has any suggestions I would greatly appreciate the help.

 

[Joe4]

Welcome to the Board!

Try this:

Sub Test()

    Select Case Sheets("Calc").Range("G11").Value
        Case 4 To 10, 12, 15, 18, 30, 80
            'do stuff
        Case Else
            'do other stuff
    End Select

End Sub

 

[K_Lucky]

Welcome to the Board!

Try this:

Sub Test()

    Select Case Sheets("Calc").Range("G11").Value
        Case 4 To 10, 12, 15, 18, 30, 80
            'do stuff
        Case Else
            'do other stuff
    End Select

End Sub

Hi Joe4,
Thanks, I have referenced this sight many times over the years as I have learned more about Excel. It is pretty much the fist place I go for things I can't figure out.

I can get your suggestion to work great in my test sub. Sorry, maybe I should have sent the bigger picture for what I am doing. Below is a simplified version of the complete sub. I am not sure how to get this to fit in with this situation.
Thanks again for your help!

 

[Joe4]

Many times we will want to copy your VBA code so we can easily work on it. However, if you post pictures of your code, that does not allow us to do that.
Please just copy and paste the text of your VBA code and then use Code Tags to format it, as shown here: How to Post Your VBA Code
It really is as simple as Copy/Paste, and then clicking a button.

How you have defined/set LDW is not legal. A variable can only be a single value unless you use an array.
So you either need to use an array, or check every value individually.

Also note that numbers should typically NOT be enclosed in double-quotes. Double-quotes are used to indicate text entries, not numeric ones.

Sorry, maybe I should have sent the bigger picture for what I am doing. Below is a simplified version of the complete sub.

Yes, there is an important lesson here. Never oversimplify your question.
Otherwise, you may can get an answer that answers the exact question you asked, but not your real problem.
No need to oversimplify things on our account.

 

[K_Lucky]

Yes, definitely lessons learned. Thanks for the advice.

I will play around with the Array you suggested and see if I can get that working.

 

[Joe4]

Sorry, maybe I should have sent the bigger picture for what I am doing. Below is a simplified version of the complete sub.

Yes, definitely lessons learned. Thanks for the advice.

I will play around with the Array you suggested and see if I can get that working.

OK, post back if you run into any trouble.
Also, if you do a Google Search on "Excel VBA arrays", you can find lots of good information and examples on it.

 

[K_Lucky]

Good afternoon,

I was able to run with your suggestion of the array and was able to recreate what I believe I had previously. I wanted to post the solution to my problem incase anyone else has a similiar problem. If you see any issues with checking cell Calc!G11 is in my array I would appreciate you letting me know.
I have tested the below and it seems to work for every number in my array.
To be honest I don't fully understand what the Ubound function is but it works.
Thanks again for your help.

Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook

Dim SCT As Variant
Dim Length As Variant
Dim RCT As Variant
SCT = 10
Length = 700
RCT = 15

Dim LDW As Variant
Dim Part_W As Variant
LDW = Array(4, 5, 6, 7, 8, 9, 10, 12, 15, 18, 30, 80)
Part_W = Filter(LDW, Range("Calc!G11"))

If Range("Calc!G10") <= SCT And Range("Calc!G12") >= Length Then
    'do stuff
    Else
    'do stuff
    End If

If Range("Calc!G10") > SCT And Range("Calc!G12") >= Length Then
    'do stuff
    Else
    'do stuff
    End If

If Range("Calc!G10") <= RCT And Range("Calc!G12") < Length And UBound(Part_W) > -1 Then
    Range("Calc!A1") = "it works"
    Else
    Range("Calc!A1") = "Nope"
    End If
    
'Elseif 'continues onto other checks