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!

 

[nicoh99]

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]

For example, the SQL might look like...

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,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

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]

OOPS, SUB VAL() FOR ISNUMERIC()

 

[SkipVought]

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,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

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,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

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]

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,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nicoh99]

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]

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]

I'd use the InStr function instead of hardcoded Mid ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nicoh99]

PHV - yes, I agree. Good catch.