Solved: Case statement

[ndendrinos]

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]

Try

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

[ndendrinos]

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]

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]

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]

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