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

K_Lucky

New Member
Joined
Nov 14, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

1699988725712.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome to the Board!

Try this:
VBA Code:
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
 
Upvote 0
Welcome to the Board!

Try this:
VBA Code:
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!

1699991298625.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top