There's got to be a better way....

P

Pete

Is there a way to make this code shorter. Either by looping or some other
function?

Sub Make_Up_1()

Range("MU_Card1").FormulaR1C1 = _
"=IF(card1=1,""A""," & _
"if(card1=11,""J""," & _
"if(card1=12,""Q""," & _
"if(card1=13,""K"",card1))))"

' Looks at Card 1 for Number Insertion

If Range("MU_Card1") = "2" Then
Sheets("Shuffle").Visible = True
Sheets("Shuffle").Select
ActiveSheet.Shapes("Two").Select
Selection.Copy
Sheets("Card1_Select").Select
Range("MU_Card1").Select
ActiveSheet.Paste
Range("MU_Card1A").Select
ActiveSheet.Paste
Selection.Name = "Two_A"
Range("MU_Card1,MU_Card1A").Select
Selection.ClearContents
Else
If Range("MU_Card1") = "3" Then
Sheets("Shuffle").Visible = True
Sheets("Shuffle").Select
ActiveSheet.Shapes("Three").Select
Selection.Copy
Sheets("Card1_Select").Select
Range("MU_Card1").Select
ActiveSheet.Paste
Range("MU_Card1A").Select
ActiveSheet.Paste
Selection.Name = "Three_A"
Range("MU_Card1,MU_Card1A").Select
Selection.ClearContents
Else

This code repeats itself 13 times total. It takes up a lot of room in my
macro files. Does anyone have an idea to make this shorter?

Thanks in advance
 
B

Bob Phillips

aryNumbers = Array("One", "Two", "Three", "Four")
Range("MU_Card1").FormulaR1C1 = "=IF(card1=1,""A""," & _
"if(card1=11,""J""," & _
"if(card1=12,""Q""," & _
"if(card1=13,""K"",card1))))"

Sheets("Shuffle").Shapes(aryNumbers(Range("MU_Card1").Value + 1)).Copy
Sheets("Card1_Select").Select
Range("MU_Card1").Select
ActiveSheet.Paste
Range("MU_Card1A").Select
ActiveSheet.Paste
Selection.Name = Range("MU_Card1").Value & "_A"
Range("MU_Card1,MU_Card1A").ClearContents


change the array to however many items ther could be.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

Pete

Wow, this works amazingly..... Thanks a bunch.

One little problem though; On testing multiple times in a row, I get a type
Mismatch error? Why is that?
 
B

Bob Phillips

Which line do you get that on?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top