Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Shorten Code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Shorten Code
Hello again, I am trying to format a worksheet by running a macro. Here are
some of my problem areas that I would like to condense if possible.

Example 1 Is a partial list of about 25 items that need to be added:
Range("H19").Select
ActiveCell.FormulaR1C1 = "Duty Groups"
Range("S19").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("Y19").Select
ActiveCell.FormulaR1C1 = "Codes"
Range("E21").Select
ActiveCell.FormulaR1C1 = "Memorial Day"
Range("E22").Select
ActiveCell.FormulaR1C1 = "Labor Day"
Range("E23").Select
ActiveCell.FormulaR1C1 = "Christmas(December 25th)"
Range("K21").Select
ActiveCell.FormulaR1C1 = "New Years Day(Jan 1st)"
Range("K22").Select
ActiveCell.FormulaR1C1 = "Independance Day(July 4th)"
Range("K23").Select
ActiveCell.FormulaR1C1 = "Thanksgiving(4th Thursday in Nov)"

Example 2 Is a partial list of about 15 employees,their shift code and
hours code:
Range("A4").Select
ActiveCell.FormulaR1C1 = "Smith"
Range("B4").Select
ActiveCell.FormulaR1C1 = "97632"
Range("C4").Select
ActiveCell.FormulaR1C1 = "A"
Range("D4").Select
ActiveCell.FormulaR1C1 = "1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Pearson"
Range("B5").Select
ActiveCell.FormulaR1C1 = "95883"
Range("C5").Select
ActiveCell.FormulaR1C1 = "B"
Range("D5").Select
ActiveCell.FormulaR1C1 = "1"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Gonzales"
Range("B6").Select
ActiveCell.FormulaR1C1 = "268603"
Range("C6").Select
ActiveCell.FormulaR1C1 = "B"
Range("D6").Select
ActiveCell.FormulaR1C1 = "2"
Any thoughts on this???
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
Art Art is offline
external usenet poster
  
Posts: 587
Default Shorten Code
Donna,

You can do it this way:

Range("H19") = "Duty Groups"
Range("S19")= "Hours"
Range("Y19") = "Codes"
Range("E21") = "Memorial Day"
Range("E22") = "Labor Day"
Range("E23") = "Christmas(December 25th)"
Range("K21") = "New Years Day(Jan 1st)"
Range("K22") = "Independance Day(July 4th)"
Range("K23") = "Thanksgiving(4th Thursday in Nov)"

The second group would be done similarly.




"Donna C" wrote:

Hello again, I am trying to format a worksheet by running a macro. Here are
some of my problem areas that I would like to condense if possible.

Example 1 Is a partial list of about 25 items that need to be added:
Range("H19").Select
ActiveCell.FormulaR1C1 = "Duty Groups"
Range("S19").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("Y19").Select
ActiveCell.FormulaR1C1 = "Codes"
Range("E21").Select
ActiveCell.FormulaR1C1 = "Memorial Day"
Range("E22").Select
ActiveCell.FormulaR1C1 = "Labor Day"
Range("E23").Select
ActiveCell.FormulaR1C1 = "Christmas(December 25th)"
Range("K21").Select
ActiveCell.FormulaR1C1 = "New Years Day(Jan 1st)"
Range("K22").Select
ActiveCell.FormulaR1C1 = "Independance Day(July 4th)"
Range("K23").Select
ActiveCell.FormulaR1C1 = "Thanksgiving(4th Thursday in Nov)"

Example 2 Is a partial list of about 15 employees,their shift code and
hours code:
Range("A4").Select
ActiveCell.FormulaR1C1 = "Smith"
Range("B4").Select
ActiveCell.FormulaR1C1 = "97632"
Range("C4").Select
ActiveCell.FormulaR1C1 = "A"
Range("D4").Select
ActiveCell.FormulaR1C1 = "1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Pearson"
Range("B5").Select
ActiveCell.FormulaR1C1 = "95883"
Range("C5").Select
ActiveCell.FormulaR1C1 = "B"
Range("D5").Select
ActiveCell.FormulaR1C1 = "1"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Gonzales"
Range("B6").Select
ActiveCell.FormulaR1C1 = "268603"
Range("C6").Select
ActiveCell.FormulaR1C1 = "B"
Range("D6").Select
ActiveCell.FormulaR1C1 = "2"
Any thoughts on this???


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Shorten Code
Thank you for the input worked great and really shortened it.

"Art" wrote:

Donna,

You can do it this way:

Range("H19") = "Duty Groups"
Range("S19")= "Hours"
Range("Y19") = "Codes"
Range("E21") = "Memorial Day"
Range("E22") = "Labor Day"
Range("E23") = "Christmas(December 25th)"
Range("K21") = "New Years Day(Jan 1st)"
Range("K22") = "Independance Day(July 4th)"
Range("K23") = "Thanksgiving(4th Thursday in Nov)"

The second group would be done similarly.




"Donna C" wrote:

Hello again, I am trying to format a worksheet by running a macro. Here are
some of my problem areas that I would like to condense if possible.

Example 1 Is a partial list of about 25 items that need to be added:
Range("H19").Select
ActiveCell.FormulaR1C1 = "Duty Groups"
Range("S19").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("Y19").Select
ActiveCell.FormulaR1C1 = "Codes"
Range("E21").Select
ActiveCell.FormulaR1C1 = "Memorial Day"
Range("E22").Select
ActiveCell.FormulaR1C1 = "Labor Day"
Range("E23").Select
ActiveCell.FormulaR1C1 = "Christmas(December 25th)"
Range("K21").Select
ActiveCell.FormulaR1C1 = "New Years Day(Jan 1st)"
Range("K22").Select
ActiveCell.FormulaR1C1 = "Independance Day(July 4th)"
Range("K23").Select
ActiveCell.FormulaR1C1 = "Thanksgiving(4th Thursday in Nov)"

Example 2 Is a partial list of about 15 employees,their shift code and
hours code:
Range("A4").Select
ActiveCell.FormulaR1C1 = "Smith"
Range("B4").Select
ActiveCell.FormulaR1C1 = "97632"
Range("C4").Select
ActiveCell.FormulaR1C1 = "A"
Range("D4").Select
ActiveCell.FormulaR1C1 = "1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Pearson"
Range("B5").Select
ActiveCell.FormulaR1C1 = "95883"
Range("C5").Select
ActiveCell.FormulaR1C1 = "B"
Range("D5").Select
ActiveCell.FormulaR1C1 = "1"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Gonzales"
Range("B6").Select
ActiveCell.FormulaR1C1 = "268603"
Range("C6").Select
ActiveCell.FormulaR1C1 = "B"
Range("D6").Select
ActiveCell.FormulaR1C1 = "2"
Any thoughts on this???


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Shorten Code
I'm not sure what you are trying to do. Below is better code. two comments
1) Number shouldn't have quotes around them unless you want to treat them as
strings. You can't add "1" + "2" on a worksheet. Number would be without
the quotes.
1) Nothing you had were for formulas. formulas are if you you were call in
functions. for example

Range("E22").FormulaR1C1 = "=sum(R3C1:R5C4)" which would sum cells A3:D5

You could also use

Range("E22").Formula = "=sum(A3:D5)"

or if you used variable

Mystartrow = 3
Myendrow = 5

Range("E22").Formula = "=sum(A" & Mystartrow & ":D" & Myendrow & ")"





Sub FormatWorksheet()

Range("H19") = "Duty Groups"
Range("S19") = "Hours"
Range("Y19") = "Codes"
Range("E21") = "Memorial Day"
Range("E22") = "Labor Day"
Range("E23") = "Christmas(December 25th)"
Range("K21") = "New Years Day(Jan 1st)"
Range("K22") = "Independance Day(July 4th)"
Range("K23") = "Thanksgiving(4th Thursday in Nov)"

'Example 2 Is a partial list of about 15 employees,
'their shift code and hours code:
Range("A4") = "Smith"
Range("B4") = 97632
Range("C4") = "A"
Range("D4") = 1
Range("A5") = "Pearson"
Range("B5") = 95883
Range("C5") = "B"
Range("D5") = 1
Range("A6") = "Gonzales"
Range("B6") = 268603
Range("C6") = "B"
Range("D6") = 2
'Any thoughts on this???
End Sub



"Donna C" wrote:

Hello again, I am trying to format a worksheet by running a macro. Here are
some of my problem areas that I would like to condense if possible.

Example 1 Is a partial list of about 25 items that need to be added:
Range("H19").Select
ActiveCell.FormulaR1C1 = "Duty Groups"
Range("S19").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("Y19").Select
ActiveCell.FormulaR1C1 = "Codes"
Range("E21").Select
ActiveCell.FormulaR1C1 = "Memorial Day"
Range("E22").Select
ActiveCell.FormulaR1C1 = "Labor Day"
Range("E23").Select
ActiveCell.FormulaR1C1 = "Christmas(December 25th)"
Range("K21").Select
ActiveCell.FormulaR1C1 = "New Years Day(Jan 1st)"
Range("K22").Select
ActiveCell.FormulaR1C1 = "Independance Day(July 4th)"
Range("K23").Select
ActiveCell.FormulaR1C1 = "Thanksgiving(4th Thursday in Nov)"

Example 2 Is a partial list of about 15 employees,their shift code and
hours code:
Range("A4").Select
ActiveCell.FormulaR1C1 = "Smith"
Range("B4").Select
ActiveCell.FormulaR1C1 = "97632"
Range("C4").Select
ActiveCell.FormulaR1C1 = "A"
Range("D4").Select
ActiveCell.FormulaR1C1 = "1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Pearson"
Range("B5").Select
ActiveCell.FormulaR1C1 = "95883"
Range("C5").Select
ActiveCell.FormulaR1C1 = "B"
Range("D5").Select
ActiveCell.FormulaR1C1 = "1"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Gonzales"
Range("B6").Select
ActiveCell.FormulaR1C1 = "268603"
Range("C6").Select
ActiveCell.FormulaR1C1 = "B"
Range("D6").Select
ActiveCell.FormulaR1C1 = "2"
Any thoughts on this???
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I shorten this any? Gregory Day Excel Worksheet Functions 1 April 10th 08 06:29 PM
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. Corey Excel Programming 3 December 11th 06 05:14 AM
Any way to shorten this up? Kevin M Excel Worksheet Functions 2 November 6th 06 07:50 PM
Shorten Code for repeated function use mel Excel Programming 4 March 8th 05 06:51 PM
Shorten Code? Rockee052[_23_] Excel Programming 5 January 28th 04 12:49 AM


All times are GMT +1. The time now is 07:50 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement