ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   There's got to be a better way.... (https://www.excelbanter.com/excel-programming/400255-theres-got-better-way.html)

Pete
There's got to be a better way....
 
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
--
Pete

Bob Phillips
There's got to be a better way....
 
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_Card 1").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)

"Pete" wrote in message
...
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
--
Pete



Pete
There's got to be a better way....
 
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?

--
Pete


"Bob Phillips" wrote:

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_Card 1").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)

"Pete" wrote in message
...
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
--
Pete




Bob Phillips
There's got to be a better way....
 
Which line do you get that on?

--
HTH

Bob

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

"Pete" wrote in message
...
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?

--
Pete


"Bob Phillips" wrote:

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_Card 1").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)

"Pete" wrote in message
...
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
--
Pete

Pete
There's got to be a better way....
 
Sheets("Shuffle").Shapes(aryNumbers(Range("MU_Card 1").Value + 1)).Copy

--
Pete


"Bob Phillips" wrote:

Which line do you get that on?

--
HTH

Bob

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

"Pete" wrote in message
...
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?

--
Pete


"Bob Phillips" wrote:

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_Card 1").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)

"Pete" wrote in message
...
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
--
Pete


All times are GMT +1. The time now is 04:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com