Cell Manipulation

[Cr4z3]

Created a simple macro that fills in each cell with specific values, i
this case names.

Cell Range - C11:C15
Names - Bob, Jim, Jake, Andrew,Kim

Sub Names()
ActiveCell.FormulaR1C1 = "Bob"
Range("C11").Select
ActiveCell.FormulaR1C1 = "Jim"
Range("C12").Select
ActiveCell.FormulaR1C1 = "Jake"
Range("C13").Select
ActiveCell.FormulaR1C1 = "Andrew"
Range("C14").Select
ActiveCell.FormulaR1C1 = "Kim"
Range("C15").Select
End Sub

Question is, how can i make it fill those cells in that same order, bu
if i start at a different cell. So remain in that Range of C11:C15 bu
loop the names in that range. So instead of starting it at C11 i migh
start at C14 but it would still fill the names in that same order, bu
when it reached the last cell in that range it would restart to th
first cell and continue filling the names

 

[duane]

how about something like this? - it assumes starting active cell i
where you want to start

Sub Names()
i = activecell.row
k = activecell.column
ActiveCell.Formula = "Bob"
i = i+1
if i >15 then i = 11
cells(i,j).formula = "Jim"
i = i+1
if i >15 then i = 11
cells(i,j).formula = "Jake"
i = i+1
if i >15 then i = 11
cells(i,j).formula = "Andrew"
i = i+1
if i >15 then i = 11
cells(i,j).formula = "Kim"
End Su

 

[Dana DeLouis]

Would any ideas here help? I don't understand the last part of the
question.

Sub Demo()
Dim Names
Names = Array("Bob", "Jim", "Jake", "Andrew", "Kim")
ActiveCell.Resize(1, UBound(Names) + 1) = Names
ActiveCell.Offset(1, 0).Select
End Sub

 

[JulieD]

Hi

just a complete different approach which may be of use is not to use code to
do this at all ...
create a custom list
(tools / options / custom lists - type the names in in the order you
want them to appear separated by a comma (,) or enter and click ADD)
then use the list
click where you want the names to start, type the first name & use the
AutoFill handle to drag the names down as far as you want.

Cheers
JulieD

 

[Dana DeLouis]

Julie. That's an excellent idea. If the op wants to use code, perhaps
something like this. There's no error checking to make sure a cell within
C11:C15 is selected.

Sub Demo()
Dim v As Variant

v = Array("Bob", "Jim", "Jake", "Andrew", "Kim")

Application.AddCustomList v

'// Pick a cell in C11:C15 to start
[C14].Select

Selection = v(0)
Selection.AutoFill Range([C11], Selection), xlFillDefault
Selection.AutoFill Range(Selection, [C15]), xlFillDefault
Application.DeleteCustomList Application.GetCustomListNum(v)
End Sub

 

[Dana DeLouis]

Oops. It needs an "On Error" in case the selection is at the beginning or
end (C11 or C15)

Sub Demo()
Dim v As Variant
[C11:C15].Clear

v = Array("Bob", "Jim", "Jake", "Andrew", "Kim")

Application.AddCustomList v

'// Pick a cell in C11:C15 to start
[C15].Select

On Error Resume Next
With Selection
.Value = v(0)
.AutoFill Range([C11], .Address)
.AutoFill Range(.Address, [C15])
End With
Application.DeleteCustomList Application.GetCustomListNum(v)
End Sub

--
Dana DeLouis
Win XP & Office 2003

Julie. That's an excellent idea. If the op wants to use code, perhaps
something like this. There's no error checking to make sure a cell within
C11:C15 is selected.

Sub Demo()
Dim v As Variant

v = Array("Bob", "Jim", "Jake", "Andrew", "Kim")

Application.AddCustomList v

'// Pick a cell in C11:C15 to start
[C14].Select

Selection = v(0)
Selection.AutoFill Range([C11], Selection), xlFillDefault
Selection.AutoFill Range(Selection, [C15]), xlFillDefault
Application.DeleteCustomList Application.GetCustomListNum(v)
End Sub

--
Dana DeLouis
Win XP & Office 2003

Hi

just a complete different approach which may be of use is not to use code
to do this at all ...
create a custom list
(tools / options / custom lists - type the names in in the order you
want them to appear separated by a comma (,) or enter and click ADD)
then use the list
click where you want the names to start, type the first name & use the
AutoFill handle to drag the names down as far as you want.

Cheers
JulieD