Number and names of courses may change later

  • Thread starter Thread starter menken.john
  • Start date Start date
M

menken.john

I'm using Excel 2010 and I have three company course names that I must add to a spreadsheet and I must do this operation routinely. I want the operation to move to the right in the spreadsheet and find the first available andempty column and then enter the course name, move right, enter another course name, etc. Here is the code that I'm using and it does work.

Code: 
'Enter the three Course names at the end of the spreadsheet
Selection.End(xlToRight).Select
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Social Styles"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Adaptive Leadership"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Leading Virtually"
Range("Q1:S1").Select
Range("S1").Activate
Selection.Font.Bold = True
Cells.Columns.AutoFit

Here's the problem. Although this code works (thanks Macro recorder), it limits me to three courses with the names above. Next year it may be four, orfive courses and they will be different names. I'm thinking that what I need is the ability to set variables as the course names then loop through those variables inserting their names in the proper places. This would allow me in the future to change only the variable names and everything would fall into place. Am I thinking about this correctly? Can anyone give me an idea what the code may look like. I do understand variables and arrays but I can't seem to piece it all together in my mind. Many thanks.
 
Hi John,

Am Sat, 10 Nov 2012 03:08:18 -0800 (PST) schrieb (e-mail address removed):
'Enter the three Course names at the end of the spreadsheet
Selection.End(xlToRight).Select
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Social Styles"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Adaptive Leadership"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Leading Virtually"
Range("Q1:S1").Select
Range("S1").Activate
Selection.Font.Bold = True
Cells.Columns.AutoFit
Click to expand...

try:

Sub Test()
Dim i As Integer
Dim FECol As Long
Dim NamesArr As Variant

NamesArr = Array("Social Styles", "Adaptive Leadership", _
"Leading Virtually")
FECol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

For i = 0 To UBound(NamesArr)
Cells(1, FECol + i) = NamesArr(i)
Next
End Sub

Regards
Claus Busch
 
Hi John,

I forgot font.bold and autofit:

Sub Test()
Dim i As Integer
Dim FECol As Long
Dim NamesArr As Variant

NamesArr = Array("Social Styles", "Adaptive Leadership", _
"Leading Virtually")
FECol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

For i = 0 To UBound(NamesArr)
With Cells(1, FECol + i)
.Value = NamesArr(i)
.Font.Bold = True
.EntireColumn.AutoFit
End With
Next
End Sub


Regards
Claus Busch
 
Your code worked flawlessly and helped me understand how to do it. Thank you very much.
 
Back
Top