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

 

[Scott Huish]

Is L21 a typo?

Here's a different version of the same idea:

Sub Case_Curr_Pairs()
Dim c As String, n As String
c = UCase(IIf(Range("D1") = "", Range("F1"), Range("D1")))
Select Case c
    Case "EUR": n = "EURUSD"
    Case "CHF": n = "USDCHF"
    Case "CAD": n = "USDCAD"
    Case "GBP": n = "GBPUSD"
    Case "AUD": n = "AUDUSD"
    Case "JPY": n = "USDJPY"
End Select
If n <> "" Then Range("L2") = n
End Sub

 

[Scott Huish]

Here is another way:

Sub Case_Curr_Pairs()
Dim c As String
c = UCase(IIf(Range("D1") = "", Range("F1"), Range("D1")))
Range("L2") = Switch( _
    c = "EUR", "EURUSD", _
    c = "CHF", "USDCHF", _
    c = "CAD", "USDCAD", _
    c = "GBP", "GBPUSD", _
    c = "AUD", "AUDUSD", _
    c = "JPY", "USDJPY")
End Sub

This could also just be done with a VLOOKUP formula on the spreadsheet.

 

[Azar]

thanks for clarifying.
so is there another way to execute a function like this?
i'm working with data sets that require these sort of equations and was hoping there was a better way to do it than long winded IF AND statements...

thanks.
a

 

[Scott Huish]

Why don't you just use a VLOOKUP formula in the spreadsheet?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	EUR	EURUSD						EUR	EURUSD
2								CHF	USDCHF
3								CAD	USDCAD
4								GBP	GBPUSD
5								AUD	AUDUSD
6								JPY	USDJPY
Sheet2

 

[Azar]

thanks again for the help!

is there a way for an IF statement, in excel, (not vba) to return/output multiple results.
so =IF(C2="USD", D2="BUY", And E3="Curr", and F4="Trade"
would something like that work.

I would try it in vba, but i cant seem to get IF statements to work for me there.

thanks