PDA

View Full Version : Solved: Case statement



ndendrinos
08-27-2009, 08:41 AM
Not sure if this is written the way it should be but the desired outcome when running the code s/b
in each cell of J1:J4 the value 11
I only get one 11 in J1
thank you

mdmackillop
08-27-2009, 10:08 AM
Try

Select Case Selection.Name
Case Is = "Picture6853", "Picture6869", "Picture6885", "Picture6895"
Columns("J:J").SpecialCells(xlCellTypeBlanks) = 11
Case Else
End Select

ndendrinos
08-27-2009, 07:04 PM
Thank you for your help mdmackillop, your suggestion fills too many cells (J1:J102)
Not sure if this will add to the problem but the first 4 cards are dealt all together (one face down (value zero as it is the back of the card) and three cards open)
Then two cards are dealt(one on top of the back of the card and one open)
after that the cards are opened one by one.
What I hope here is that the values assigned to each picture will be shown in J:J in the order they are dealt.

ndendrinos
08-29-2009, 07:51 PM
Well today I had an idea to put all the cases together and simplify the code and Peter (VoG) came up with a solution that works and is shown here.
His original code produced the same result as mdmackillop's (J1:J102) instead of just J1 ... further edit to the code addressed this.


Function NameToNumber(s As String) As Integer
Select Case s
Case "Pictureas", "Pictureac", "Pictureah", "Picturead": NameToNumber = 11
Case "Picture2s", "Picture2c", "Picture2h", "Picture2d": NameToNumber = 2
Case "Picture3s", "Picture3c", "Picture3h", "Picture3d": NameToNumber = 3
Case "Picture4s", "Picture4c", "Picture4h", "Picture4d": NameToNumber = 4
Case "Picture8d": NameToNumber = 8
Case "Picturejh": NameToNumber = 10
End Select
End Function


Sub testinprogress()
Dim i As Integer
Application.ScreenUpdating = False

ActiveSheet.Shapes(Range("B1").Value).Copy
Range("C1").Select
ActiveSheet.Paste
i = NameToNumber(Selection.Name)
Columns("J").SpecialCells(xlCellTypeBlanks)(1).Value = i
End Sub
Thank again for your help.

Here is the link to VoG's reply:
http://www.mrexcel.com/forum/showthread.php?t=412899

mdmackillop
08-30-2009, 04:30 AM
How about

Function NameToNumber(s As String) As Integer
Select Case Mid(s, 8, 1)
Case "a": NameToNumber = 11
Case "1", "j", "q", "k": NameToNumber = 10
Case Else: NameToNumber = Mid(s, 8, 1)
End Select
End Function

ndendrinos
08-30-2009, 09:10 AM
I find myself in a delicate situation here.
Your code mdmckillop is just perfect and way shorter than Peter's solution .
I can only hope that he will visit this posting via the shotcut I left at MrExcel.
I for one grateful for ALL the help I get will not bring this to his attention.
Thank you and have a good day, Nick