Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > There's got to be a better way....
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 193
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 193
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 193
Default 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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 02:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement