ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Shorten Code (https://www.excelbanter.com/excel-programming/386649-shorten-code.html)

Donna C
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???

Art
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???

Donna C
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???

joel
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???

Donna C
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???




Gary Keramidas
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???

Dana DeLouis
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???

Donna C
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???

Donna C
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???

Donna C
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???

Donna C
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???


All times are GMT +1. The time now is 05:23 PM.

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