Get a SELECT CASE range from an Excel cell 1

[nicoh99]

Hey guys,

I am trying to pull a range from an Excel cell and plug it into a SELECT statement in my VBA code. For example, cell A1 has 33,34,35 in it.

Sample Code:
exampleNum = 34

Select Case exampleNum
Case Range("A1").Value
EXanswer = "Yes"
Case Else
EXanswer = "No"
End Select

Normally if I just had:
Case 33,34,35
Then the 34 would be recognized in the range, however when I plug in the Range("A1"), it is plugged in as a string, thus it is comparing 34 to "33,34,35" as a whole and not 33,34,35. I have tried numerous things to convert to numeric/drop the quotes, etc, but no luck. Any thoughts?

Thanks in advance!

 

[SkipVought]

Hi,

exampleNum = 34

Select Case Range("A1").Value
     Case 34, 35, 36
           EXanswer = "Yes"
     Case Else
           EXanswer = "No"
End Select

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

Hey Skip,

The reason I am trying to pull this info from an Excel cell is so the end user can change the ranges on demand. I am pulling an account number from a database and then looping through the excel cells to get the ranges to determine where to plug in the data.

Thanks,

Nic

 

[SkipVought]

Why not use a spreadsheet lookup. Get you there real fast without code, because it does not seem as if you know what you are doing. I do not see any ranges in the code you posted, other than A1.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

Skip,

I was just using A1 as an example. I have different ranges in different cells (G1, G2, G3...). I have to use code because I am connecting to a sql server database. I must admit I am not very good at explaining things, so instead of an example, here is a snippet of my actual code.

For acct = 0 To UBound(arrayAcct, 2)
acctFound = False
i = 8
Do Until Range("G" & i) = "End" Or acctFound = True
Select Case arrayAcct(0, acct)
Case Range("G" & i).Text
'Get Montly Figure
Range("B" & i).Value = arrayAcct(cMnth, acct)
acctFound = True
Case Else
i = i + 1
End Select
Loop
Next

Thanks,

Nic

 

[SkipVought]

I have to use code because I am connecting to a sql server database.

That is not necesssarily true. I query Oracle, DB2, MS Access, other Excel workbooks, without a bit of VBA code. Then there are instances when I DO use VBA code to access external databases.

Please explain further what you are trying to do, beyond finding values stored in an array. There are much much more efficient ways to locate data.

Maybe, start with the intent of your application.



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

Basically, I'm just trying to figure out how to take a value or range from an Excel cell:

Example
G1 contains 1,2,3,4
G2 contains 5 to 15
G3 contains 16

and plug it into a SELECT CASE statement (in the Case = area)where it is not plugged in as a single string, but as a range or value.

With the examples given above I get:
"1,2,3,4"
"5 to 15"
16

The 16 example works fine right now because it is recognized as numeric. However, the other two are "looked" at as being a single string ("1,2,3,4") and not a range(1,2,3,4)(emphasis on the quotes!). Thus, when I am looking for a case with the value of 3, it is not found.

 

[SkipVought]

What is the PURPOSE for this exersize? This is an extremely unorthodox requirement.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

single string ("1,2,3,4") and not a range(1,2,3,4)

FYI, range(1,2,3,4) is NOT a range. A "range" has a ver specific meaning in VBA. 1,2,3,4 can be an array, but normally, a array is stored in a "Range": one value per cell of the range. So G1:J1 might contain these four values, NOT one cell containing what you intend to be four values.

But I still need to know the INTENT of your questions.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

The purpose is to have somewhat of a floating Select Case statement so that way if I need to loop through 20 cells or 100 cells, I can do that while also giving the user the flexibility to change the range criteria in any given cell (which would in turn "update" the case criteria).

 

[SkipVought]

You are stating the METHOD to achieve a task.

I need to understand WHY you THINK that you need to do this. Forget CASE!

Why is the user interacting with your application?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

(1,2,3,4) IS a range when used in a Case Statement as a criteria

SELECT CASE arrayVariable
Case 1,2,3,4
Do something if arrayVariable is in the RANGE
1,2,3,4
Case 5 to 8
Do something else if arrayVariable is in the RANGE
from 5 to 8 (either 5,6,7, or 8)

 

[SkipVought]

Just know that when you use RANGE in this forum, it will be interpreted as an applcation object. Yes, a set of values can be refered to as a range. I'm trying to help you out here, so we can understand each other.

BOTTOM LINE, 1,2,3,4 in a cell will not be interpreted as a range in the terms that you intend. It is ONE VALUE, that you will have to parse to become multiple values. But there would be no way to get those values to become criteria in a Select Case statement. There are other ways to evaluate on-the-fly selections.

What's the INTENT?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

Skip,

I'm not sure what else to tell you as far as my intent. I'm sorry if I came off as rude, I have been stewing over this issue since last night and it has really got me flustered. I figured there would be an obvious easy answer that I was just overlooking and someone else would say, hey silly, do this. I think you answered my question though in the previous post. I will go about it a different way. Thanks again!

 

[mintjulep]

Although we might be beyond that now, the following is a (ugly) solution to your example problem in your first post.

Public Function exanswer()

exampleNum = 34

myel = Split(Range("A1").Value, ",")


Select Case exampleNum
     Case CInt(myel(0)), CInt(myel(1)), CInt(myel(2))
           exanswer = "Yes"
     Case Else
           exanswer = "No"
End Select
End Function

 

[SkipVought]

Here are some reasons why you do not want to go this route.

Apparently the USER enters....
[tt]
G1: 1,2,3,4
G2: 5 to 15
[/tt]
So what happens if the user enters DECIMAL or SLASH or some other delimiter?

What happens of the user enters two or too or t o or anything other than to?

Beyond these uncertainties, there is no way to get your values into the Select criteria.

What's the INTENT?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

mint,

But the WHOLE reason for this is flexibility. Your solution is RIGID, allowing only 3 values. What if the user enters 1,2,3,4,5,10 to 8, <100 in G1???

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

Yes.

My solution satisfies the functional requirement implied in the example in the OP, and only that functional requirement.

Without undermining what you have been saying Skip, I think the problem is that the select case statement expects an expressionlist after "CASE", and an expression list is a data type that exists only within the SELECT CASE statement.

I suspect that you could create an expressionlist class, and use that in the CASE statement. You'd still need a bullet-proof way to parse the cell value.

 

[SkipVought]

I'd venture a guess that if the OP has a properly structured table, the criteria could easily be processed by a MS Query, using the "range" in an IN statement and changing the "5 to 15" format to a FROM cell and TO cell or parsing in a Split to get the first and last elements, placing those values in a BETWEEN statement; less than 10 minutes worth of coding and structuring.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HarleyQuinn]

Just another point to add to this already busy thread, if you're going to be taking free user text to relate to cells error handle it well and sanity check the input to make sure it's within Excel's allowed row and column ranges (depending on your version of Excel).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.