| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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??? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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??? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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??? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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??? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
I am still pretty new to this so, you could put what I know about "arrays" on
a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
since you're not familiar, here's an entire sub you can just paste in a blank workbook. my months are in columns B-M and the days start in row 4. just watch for word-wrap issues when you paste the code in your workbook. Sub Holidays2() Dim i As Long Dim cell As Range Dim arr As Variant i = 0 arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") For Each cell In _ ActiveSheet.Range("B19,C23,F32,H7,J7,K12,L13,L26,L 27,M28") cell.Value = arr(i) i = i + 1 Next End Sub -- Gary "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
Here's how I would do your second example:
'1 Line [A4:D4] = [{"Smith","97632","A",1}] 'or 2 Lines [A5:D6] = [{"Pearson",95883,"B",1;"Gonzales",268603,"B",2}] If you go crazy with too large an array, it can be hard to read and debug. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
Thanks again for the input.
I am doing something wrong though apparently. When I put your code in I keep getting error messages. After "Labor", I put a ) and & before the underscore. On the next line before "Columbus" I put ( I have tried several ways but still getting it wrong. Donna C. "Gary Keramidas" wrote: since you're not familiar, here's an entire sub you can just paste in a blank workbook. my months are in columns B-M and the days start in row 4. just watch for word-wrap issues when you paste the code in your workbook. Sub Holidays2() Dim i As Long Dim cell As Range Dim arr As Variant i = 0 arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") For Each cell In _ ActiveSheet.Range("B19,C23,F32,H7,J7,K12,L13,L26,L 27,M28") cell.Value = arr(i) i = i + 1 Next End Sub -- Gary "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
Thank you for the additional input.
Thats really sweet and works great. Donna C "Dana DeLouis" wrote: Here's how I would do your second example: '1 Line [A4:D4] = [{"Smith","97632","A",1}] 'or 2 Lines [A5:D6] = [{"Pearson",95883,"B",1;"Gonzales",268603,"B",2}] If you go crazy with too large an array, it can be hard to read and debug. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
Dana, when using this format is it strictly for 2 lines??
I got greedy and tried to spread from A4 to D16. What I end up with is: #VALUE! in my cells???? Donna C "Dana DeLouis" wrote: Here's how I would do your second example: '1 Line [A4:D4] = [{"Smith","97632","A",1}] 'or 2 Lines [A5:D6] = [{"Pearson",95883,"B",1;"Gonzales",268603,"B",2}] If you go crazy with too large an array, it can be hard to read and debug. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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??? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorten Code
regarding the previous response I submitted, I figured out the problem.
I put a , instead of ; Works great now. Donna c "Dana DeLouis" wrote: Here's how I would do your second example: '1 Line [A4:D4] = [{"Smith","97632","A",1}] 'or 2 Lines [A5:D6] = [{"Pearson",95883,"B",1;"Gonzales",268603,"B",2}] If you go crazy with too large an array, it can be hard to read and debug. -- HTH :) Dana DeLouis Windows XP & Office 2007 "Donna C" wrote in message ... I am still pretty new to this so, you could put what I know about "arrays" on a pin head. I will try your code in a new workbook and try to understand what it does.Thanks for the input. Donna C. "Gary Keramidas" wrote: i use this for a vacation calendar i wrote arr = Array("MLK Day", "W/L Bday", "Memorial", "July 4th", "Labor", _ "Columbus", "Veteran's", "TGiving", "Closed", "Christmas") i = 0 'For i = LBound(arr) To UBound(arr) For Each cell In Worksheets("Blank Form").Range("B19,C23,F32,H7,J7,K12,L13,L26,L27,M2 8") cell.Value = arr(i) cell.Font.Bold = True cell.HorizontalAlignment = xlCenter cell.Font.Size = 8 cell.Font.ColorIndex = 3 i = i + 1 Next -- Gary "Donna C" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I shorten this any? | Excel Worksheet Functions | |||
| Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
| Any way to shorten this up? | Excel Worksheet Functions | |||
| Shorten Code for repeated function use | Excel Programming | |||
| Shorten Code? | Excel Programming | |||
Linear Mode
