Case Select

A

Azar

New Member
Joined
Jun 28, 2011
Messages
28
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hello and welcome to the board! This seemed to work for me:


Code: 
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
 
Upvote 0
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
Code: 
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
 
Upvote 0
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
 
Upvote 0
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:


Code: 
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....
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
Foot meet mouth and thanks for the clarification HOTPEPPER and JackDanIce!
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Select Case in VBA
Replies
3
Views
737
rollis13
rollis13
A
  • Solved
Changing recorded macro to have dynamic selection of cells when trying to format a sheet.
Replies
4
Views
885
Michael M
Michael M
A
  • Question Question
Help with existing code in Post - VBA to Create Org Chart
Replies
1
Views
2K
mrpotato562
M
C
  • Solved
Problem extracting data from large .txt file
Replies
5
Views
1K
DanteAmor
DanteAmor
S
  • Question Question
compile error duplicate declaration in current scope to solve
Replies
1
Views
907
Fluff
Fluff

Forum statistics

Threads
1,224,504
Messages
6,179,142
Members
452,892
Latest member
JUSTOUTOFMYREACH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top