Case Select

[Azar]

Hi-
I entered the code below to look for specific text in 1 of 2 cells.
If that text is found, I want excel to display a specific output in a different cell.
When i run this macro, nothing happens, no errors, but no results on the worksheet.
any ideas? what am i missing here?
this is my first attempt at using Case function.

thanks,
Sub Case_Curr_Pairs()

Select Case Range("D1", "F1").Text

Case "EUR"
Range("L2").Value = EURUSD
Case "CHF"
Range("L2").Text = "USDCHF"
Case "CAD"
Range("L2").Text = "USDCAD"
Case "GBP"
Range("L21").Text = "GBPUSD"
Case "AUD"
Range("L2").Text = "AUDUSD"
Case "JPY"
Range("L2").Text = "USDJPY"

End Select

 

[sous2817]

Hello and welcome to the board! This seemed to work for me:

Sub test()
Select Case Range("D1").Value Or Range("F1").Value
    Case "EUR"
        Range("L2").Value = "EURUSD"
    Case "CHF"
        Range("L2").Value = "USDCHF"
    Case "CAD"
        Range("L2").Value = "USDCAD"
    Case "GBP"
        Range("L2").Value = "GBPUSD"
    Case "AUD"
        Range("L2").Value = "AUDUSD"
    Case "JPY"
        Range("L2").Value = "USDJPY"
End Select
End Sub

 

[JackDanIce]

Read through this link:
http://www.ozgrid.com/VBA/select-case.htm

As far as I'm aware, you can only evaluate a single condition for your SELECT CASE statement.

As a work around, this works but places priority on D1 having a value

Sub Case_Curr_Pairs()

Dim CCY As String
Application.ScreenUpdating = False

If IsEmpty(Range("D1")) Then
    CCY = Range("F1")
Else
    CCY = Range("D1")
End If

Select Case CCY
    Case "EUR"
        Range("L2") = "EURUSD"
    Case "CHF"
        Range("L2") = "USDCHF"
    Case "CAD"
        Range("L2") = "USDCAD"
    Case "GBP"
        Range("L2") = "GBPUSD"
    Case "AUD"
        Range("L2") = "AUDUSD"
    Case "JPY"
        Range("L2") = "USDJPY"
    Case Else
        Range("L2").ClearContents
        MsgBox "No values in either D1 or F1"
End Select

Application.ScreenUpdating = True

End Sub

 

[Azar]

thanks.
I updated as per below, (pasted again below)
but now I get a 'type mismatch' error on this line:

Select Case Range("D1").Value Or Range("F1").Value

thanks for your help.

Here's the code:
Sub Case_Curr_Pairs()

Select Case Range("D1").Value Or Range("F1").Value

Case "EUR"
Range("L2").Value = "EURUSD"
Case "CHF"
Range("L2").Value = "USDCHF"
Case "CAD"
Range("L2").Value = "USDCAD"
Case "GBP"
Range("L21").Value = "GBPUSD"
Case "AUD"
Range("L2").Value = "AUDUSD"
Case "JPY"
Range("L2").Value = "USDJPY"

End Select

End Sub

 

[sous2817]

hmmmm...well isn't this interesting....Try this and it works, but only for the True statement...type in True in either D1 or F1:

Sub test()
Select Case Range("D1").Value Or Range("F1").Value
    Case "EUR"
        Range("L2").Value = "EURUSD"
    Case "CHF"
        Range("L2").Value = "USDCHF"
    Case "CAD"
        Range("L2").Value = "USDCAD"
    Case "GBP"
        Range("L2").Value = "GBPUSD"
    Case "AUD"
        Range("L2").Value = "AUDUSD"
    Case "JPY"
        Range("L2").Value = "USDJPY"
    Case True
        Range("L2").Value = "Why does this work?!"
End Select
End Sub

I'll admit, when I posted my code, I just used True as my select statement check and it worked. I didn't try it with your data. I just assumed that since it worked for True, it would work for other things....

 

[JackDanIce]

Pretty sure it's for the reason I've given - SELECT CASE only evaluates a single variable that itself then has multiple variables; effectively a one-to-many mapping.

Azar is trying to get SELECT CASE to work as a many-to-many mapping which I don't think is possible (perhaps one of the moderators, MVP's can give their thoughts)

 

[Scott Huish]

It works because your are doing an OR with TRUE. If either value is TRUE, it will return TRUE, it can't possibly return any of the other values with the OR.

BTW, you may want to couple your Select Case line with UCASE, otherwise you would only match EUR for example, not eur. It is case sensitive.

 

[sous2817]

Foot meet mouth and thanks for the clarification HOTPEPPER and JackDanIce!

 

[Azar]

thanks all for the help.

Hotpepper- I didnt fully understand your comment.
are you explaining why the way that sous2817 did it wont work for me.
Is JackDanIce correct that this type of logic cant be executed?

thanks again

 

[Scott Huish]

Or is a Boolean function, it returns TRUE and FALSE results

so,

if one of the cells contained TRUE it would return TRUE then it would execute that line.

JackDanIce gave you the answer. Select Case can only evaluate one Expression.

TRUE Or Blank is one expression because it only returns one answer.