Shorten Code

[Guest]

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

 

[Guest]

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.

 

[Guest]

Thank you for the input worked great and really shortened it.

 

[Guest]

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 A35

You could also use

Range("E22").Formula = "=sum(A35)"

or if you used variable

Mystartrow = 3
Myendrow = 5

Range("E22").Formula = "=sum(A" & Mystartrow & "" & 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

 

[Dana DeLouis]

I would like to condense if possible

Would this be of interest?

[E21:E23] = [{"Memorial"; "Labor"; "Christmas"}]

 

[Dave Peterson]

And if you like Dana's suggestion, you may like this variation:

range("E21").resize(3,1).value _
= application.transpose(array("Memorial", "Labor", "Christmas"))

I would like to condense if possible

Would this be of interest?

[E21:E23] = [{"Memorial"; "Labor"; "Christmas"}]

--
Dana DeLouis
Windows XP & Office 2007

Thank you for the input worked great and really shortened it.

 

[Guest]

Nice!

And if you like Dana's suggestion, you may like this variation:

range("E21").resize(3,1).value _
= application.transpose(array("Memorial", "Labor", "Christmas"))

I would like to condense if possible

Range("E21") = "Memorial Day"
Range("E22") = "Labor Day"
Range("E23") = "Christmas(December 25th)"

Would this be of interest?

[E21:E23] = [{"Memorial"; "Labor"; "Christmas"}]

--
Dana DeLouis
Windows XP & Office 2007

Thank you for the input worked great and really shortened it.

:

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.




:

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]

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,M28")

cell.Value = arr(i)
cell.Font.Bold = True
cell.HorizontalAlignment = xlCenter
cell.Font.Size = 8
cell.Font.ColorIndex = 3
i = i + 1
Next

 

[Guest]

Thank you for the different idea. I will give it a go tommorow.
Donna C

I would like to condense if possible

Would this be of interest?

[E21:E23] = [{"Memorial"; "Labor"; "Christmas"}]

--
Dana DeLouis
Windows XP & Office 2007

Thank you for the input worked great and really shortened it.

 

[Guest]

Thank you for your response as well. I will try tommorow.
Donna C.

And if you like Dana's suggestion, you may like this variation:

range("E21").resize(3,1).value _
= application.transpose(array("Memorial", "Labor", "Christmas"))

I would like to condense if possible

Range("E21") = "Memorial Day"
Range("E22") = "Labor Day"
Range("E23") = "Christmas(December 25th)"

Would this be of interest?

[E21:E23] = [{"Memorial"; "Labor"; "Christmas"}]

--
Dana DeLouis
Windows XP & Office 2007

Thank you for the input worked great and really shortened it.

:

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.




:

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

 

[Guest]

Sorry I didn't explain in better detail. The section that has numbers are
only being used to assign an employee a specific duty group and an employee
number,no calculations are needed.I appreciate the input though.
Donna C.

 

[Guest]

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]

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,L27,M28")
cell.Value = arr(i)
i = i + 1
Next
End Sub

 

[Dana DeLouis]

Here's how I would do your second example:

'1 Line
[A44] = [{"Smith","97632","A",1}]

'or 2 Lines
[A56] = [{"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.

 

[Guest]

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.

 

[Guest]

Thank you for the additional input.
Thats really sweet and works great.
Donna C

Here's how I would do your second example:

'1 Line
[A44] = [{"Smith","97632","A",1}]

'or 2 Lines
[A56] = [{"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

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.

 

[Guest]

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

Here's how I would do your second example:

'1 Line
[A44] = [{"Smith","97632","A",1}]

'or 2 Lines
[A56] = [{"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

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.

 

[Guest]

regarding the previous response I submitted, I figured out the problem.
I put a , instead of ;
Works great now.
Donna c

Here's how I would do your second example:

'1 Line
[A44] = [{"Smith","97632","A",1}]

'or 2 Lines
[A56] = [{"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

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.