Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

nicoh99 (Programmer) (OP)
16 Mar 09 19:06
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 (Programmer)
16 Mar 09 19:13



Hi,

CODE

exampleNum = 34

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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 19:27
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 (Programmer)
16 Mar 09 19:30




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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 19:43
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 (Programmer)
16 Mar 09 19:52


Quote:

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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 20:06
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 (Programmer)
16 Mar 09 20:11



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

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

SkipVought (Programmer)
16 Mar 09 20:18



Quote:

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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 20:18
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 (Programmer)
16 Mar 09 20:20



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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 20:27
(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 (Programmer)
16 Mar 09 20:39




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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
16 Mar 09 20:47
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!
Helpful Member!  mintjulep (TechnicalUser)
16 Mar 09 20:50
Although we might be beyond that now, the following is a (ugly) solution to your example problem in your first post.

CODE

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 (Programmer)
16 Mar 09 20:53




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

Apparently the USER enters....

G1:  1,2,3,4
G2:  5 to 15

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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

SkipVought (Programmer)
16 Mar 09 20:56



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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

mintjulep (TechnicalUser)
16 Mar 09 21:11
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 (Programmer)
17 Mar 09 8:47


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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

HarleyQuinn (Programmer)
17 Mar 09 8:58
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: How to get the best answers before posting.

nicoh99 (Programmer) (OP)
17 Mar 09 12:47
I looked at going this route because:
1. Only one person will be using this. They are very capable of putting in the correct values, ranges, or whatever you would like to refer to them as.
2. More than likely, they won't need to be changed, but just  in case, I'd thought it be nice to have that flexibility.

That being said, what I was looking for was a way to shed the quotes from the cell value when pulling it over into my code. Have the code look at it as if I had typed what was in that cell in my select case statement.  
SkipVought (Programmer)
17 Mar 09 13:04


For example, the SQL might look like...

CODE

Dim sSQL As String, sFrom As String, sTo As String

sFrom = Split([G2], " ")(0)
sTo = Split([G2], " ")(UBound(Split([G2], " ")))

sSQL = "Select * "
sSQL = sSQL & "From [YourSheetName$] "
sSQL = sSQL & "Where [SomeField] IN (" & [G1] & ")"
sSQL = sSQL & "   OR [SomeField] Between " & sFrom & " AND " & sTo

Debug.Print sSQL
 

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
17 Mar 09 17:42
I think we are getting close. The Split works great, but it is still like I am comparing a text to a numeric. Also (not sure if it makes a difference in this case) I am attempting this in a select case statement and not a select sql statement.

It would kind of be like:
matchToMeVal = 5000

exVal = Split([G2],",")(0) 'Let's say 5000,5001 was in G2,  
                           'so exVal would equal 5001

Select Case matchToMeVal
     Case exVal
         msgbox "yes, it matches!!!"
     Case Else
         msgBox "Nope, you're still S.O.L."
End Select

When I try this, matchToMeVal and exVal aren't recognized as being the same because it sees exVal as "5000"(text) and matchToMeVal as 5000(numeric). I guess at this point, I could make matchToMeVal a string, but let's just say I'd really like to make exVal a numeric instead. I guess I could I then do:

IsNumeric(exVal)

If so, great. I think that will solve my problem. Even if I have a "5000 To 5009" I could use the Split function to get the 5000 and 5009 and make it a range(sorry if I'm using the incorrect terminology here) like:

Case >= exValStart AND <= exValEnd

I guess I'll find out soon enough.
Thanks!
nicoh99 (Programmer) (OP)
17 Mar 09 17:48
OOPS, SUB VAL() FOR ISNUMERIC()
SkipVought (Programmer)
17 Mar 09 19:27



You're banging you head against the wall, pal.

A query would be sooooomuch more simple, from the little I know about what your INTENT is.

Its like you're trying to use a hammer to do the job of a screw driver.  Of course, you can hammer a screw, but what craftsman would even THINK of doing that? You have decided to use a hammer, come hell or hell water.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

SkipVought (Programmer)
18 Mar 09 8:49



I really like the shop tool analogies, cuz most of us can relate.

I've used my pen knife to tighten a screw holding the earpiece of my glasses.  It is certainly not the recommended tool, but it works in a pinch.

The key phrase is in a pinch. Gotta do it quick. Want to do it quick.  Grab the best thing at hand that works. A table knife would not work at all, neither would a dime, both of which might work for other kinds of screws.

It would be stupid of me to insist on using any knife, if this were a production situation.  I'd spend some time rummaging in my tool cabinet to find my jewelers set, or I'd lay out some cash to buy the appropriate tools.  In some case, I may even have to spend some time and cash to learn how to use a new tool.  I have done it MANY TIMES.

AWTTWIS.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
18 Mar 09 9:24
Skip,

At this point, I think you are just being an ass. I tried to have respect and tact even when you replied back with rude remarks. It looks like you've been a member on here since I was a sophomore in college, so I'm not claiming to know more or how to program better than you. I realize there are a zillion other ways to go about it, and mine probably isn't the best. But I can't program the way Skip understands or prefers, I program how it makes sense to me. When I posted a question on here, I really didn't ask or want for you to rewrite my entire code. I simply wanted an ANSWER to my question. I can definitely see why you have 25,253 posts and counting. So the next time you go to "help" somebody (emphasis on the quotes yet again), maybe read the question and try to give a direct answer.

mintjulep - Thanks for the help.


SkipVought (Programmer)
18 Mar 09 9:43


nicoh99,

We are all waiting for you to post your solution.  I learn new things every day, and I do not claim to know it all. I am indeed curious to know how you use the Select Case construct with the kinds of on-the-fly criteria you have posted.

I give direct answers when it makes sense. I often probe to discover the real question.  As a programmer/analyst of 30+ years, I have found most often, that the question is not really THE question. Finding the REAL question and working with the user to achieve a solid solution IS my job, because I care.  And that is why I am sucessful at what I do, in the Aerospace Industry and on Tek-Tips.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

nicoh99 (Programmer) (OP)
18 Mar 09 10:29
Not sure how well it will post over, but here it is:

i = 8
If recExists = True Then
    Do Until Range("G" & i) = "End"
        If Len(Range("G" & i)) > 4 Then
            If Mid(Range("G" & i), 5, 1) = "," Or Mid(Range("G" & i), 6, 1) = "," Then
                arrayRange = Split(Range("G" & i), ",")
                For r = 0 To UBound(arrayRange)
                    acctVal = arrayRange(r)
                    For acct = 0 To UBound(arrayAcct, 2)
                        If arrayAcct(0, acct) = acctVal Then
                            'Get Montly Figure
                            Range("B" & i).Value = Range("B" & i).Value + (arrayAcct(creditMonth, acct) - arrayAcct(debitMonth, acct))
                            'Get Period to Date Figure (array fields 6-18 19-31)
                            Range("D" & i).Value = Range("D" & i).Value + ((arrayAcct(6, acct) + arrayAcct(7, acct) + arrayAcct(8, acct) + arrayAcct(9, acct) + arrayAcct(10, acct) + arrayAcct(11, acct) + arrayAcct(12, acct) + arrayAcct(13, acct) + arrayAcct(14, acct) + arrayAcct(15, acct) + arrayAcct(16, acct) + arrayAcct(17, acct) + arrayAcct(18, acct)) - (arrayAcct(19, acct) + arrayAcct(20, acct) + arrayAcct(21, acct) + arrayAcct(22, acct) + arrayAcct(23, acct) + arrayAcct(24, acct) + arrayAcct(25, acct) + arrayAcct(26, acct) + arrayAcct(27, acct) + arrayAcct(28, acct) + arrayAcct(29, acct) + arrayAcct(30, acct) + arrayAcct(31, acct)))
                            Exit For
                        End If
                    Next
                Next
            Else
                If Mid(Range("G" & i), 5, 2) = "TO" Or Mid(Range("G" & i), 6, 2) = "TO" Then
                    RangeStart = Split(Range("G" & i), "TO")(0)
                    RangeEnd = Split(Range("G" & i), "TO")(1)
                    For acct = 0 To UBound(arrayAcct, 2)
                        If Val(arrayAcct(0, acct)) >= Val(RangeStart) And Val(arrayAcct(0, acct)) <= Val(RangeEnd) Then
                            'Get Montly Figure
                            Range("B" & i).Value = Range("B" & i).Value + (arrayAcct(creditMonth, acct) - arrayAcct(debitMonth, acct))
                            'Get Period to Date Figure (array fields 6-18 19-31)
                            Range("D" & i).Value = Range("D" & i).Value + ((arrayAcct(6, acct) + arrayAcct(7, acct) + arrayAcct(8, acct) + arrayAcct(9, acct) + arrayAcct(10, acct) + arrayAcct(11, acct) + arrayAcct(12, acct) + arrayAcct(13, acct) + arrayAcct(14, acct) + arrayAcct(15, acct) + arrayAcct(16, acct) + arrayAcct(17, acct) + arrayAcct(18, acct)) - (arrayAcct(19, acct) + arrayAcct(20, acct) + arrayAcct(21, acct) + arrayAcct(22, acct) + arrayAcct(23, acct) + arrayAcct(24, acct) + arrayAcct(25, acct) + arrayAcct(26, acct) + arrayAcct(27, acct) + arrayAcct(28, acct) + arrayAcct(29, acct) + arrayAcct(30, acct) + arrayAcct(31, acct)))
                        End If
                    Next
                End If
            End If
        Else
            acctVal = Range("G" & i)
                For acct = 0 To UBound(arrayAcct, 2)
                    If Val(arrayAcct(0, acct)) = acctVal Then
                        'Get Montly Figure
                        Range("B" & i).Value = Range("B" & i).Value + (arrayAcct(creditMonth, acct) - arrayAcct(debitMonth, acct))
                        'Get Period to Date Figure (array fields 6-18 19-31)
                        Range("D" & i).Value = Range("D" & i).Value + ((arrayAcct(6, acct) + arrayAcct(7, acct) + arrayAcct(8, acct) + arrayAcct(9, acct) + arrayAcct(10, acct) + arrayAcct(11, acct) + arrayAcct(12, acct) + arrayAcct(13, acct) + arrayAcct(14, acct) + arrayAcct(15, acct) + arrayAcct(16, acct) + arrayAcct(17, acct) + arrayAcct(18, acct)) - (arrayAcct(19, acct) + arrayAcct(20, acct) + arrayAcct(21, acct) + arrayAcct(22, acct) + arrayAcct(23, acct) + arrayAcct(24, acct) + arrayAcct(25, acct) + arrayAcct(26, acct) + arrayAcct(27, acct) + arrayAcct(28, acct) + arrayAcct(29, acct) + arrayAcct(30, acct) + arrayAcct(31, acct)))
                        Exit For
                    End If
                Next
        End If
        i = i + 1
    Loop
End If

Yes, you will notice I did not use a Select Case statement since I was unable to directly plug the values of the cell into the Case criteria (which was what I was originally asking about), but instead used the Split function (A good alternative).  
nicoh99 (Programmer) (OP)
18 Mar 09 10:31
Lengthy, yes, but it gets the job done and I do not notice a lag when I run the code. With those two criteria being met, I'm happy.
PHV (MIS)
18 Mar 09 10:58
nicoh99 (Programmer) (OP)
18 Mar 09 11:24
PHV - yes, I agree. Good catch.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close