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
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
Thank you for your help
Try
[VBA]
Select Case Selection.Name
Case Is = "Picture6853", "Picture6869", "Picture6885", "Picture6895"
Columns("J:J").SpecialCells(xlCellTypeBlanks) = 11
Case Else
End Select
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Thank you for your help
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.
Thank again for your help.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
Here is the link to VoG's reply:
http://www.mrexcel.com/forum/showthread.php?t=412899
Thank you for your help
How about
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Thank you for your help