Populating a range of empty cells with usernames

Hi
1. You have used cell as a variable, so need to declare it. Cells is a
vba term so it might be better to use mycell for example

Dim mycell as Range

2. Your loop will not change the ActiceCell so once your loop puts a
value in it the loop will ignore it after that.

3. Your range newnames has 4 cells in it. Your code line

Worksheets("Sheet3").ActiveCell = newnames(n)

will fill the ActiveCell with data from newnames when n is 1, 2, 3 or
4 (the data in A1 to A4). Your loop, however, is cycling through the
range A1 to l9 one cell at a time (across rows then down) which is 12
times 9 = 108 cells. Your If condition inside this 108 cycle loop is
incrementing n, so once n goes above 4 you will get an error as
newnames(n) won't make sense. I suspect you want

Worksheets("Sheet3").ActiveCell = newnames(n)

inside your inner if..then..else?
A tentative guess at the code you really want is:


Private Sub CommandButton1_Click()

Dim oldnames As Range
Dim newnames As Range
Dim myCell as Range
Dim x As Integer
Dim n As Integer
Dim i As String
Dim k As Integer

Set newnames = Worksheets("Sheet2").Range("A1:A4")

n = 1
Worksheets("Sheet3").Activate

For Each myCell In Worksheets("Sheet3").Range("A1:I9")
myCell.Activate
If Trim(ActiveCell.Value) = "" Then

If (n <= 2) Then
Worksheets("Sheet3").ActiveCell.Value = newnames(n)
n = n + 1
Else
n = 1
End If
End If
Next myCell


UserForm1.Hide


End Sub

This will only allow n to take the values 1 or 2 however, which may
still not be what you really want.
regards
Paul




On Feb 23, 5:50 am, DD wrote:

Hello,

I am trying to make an array of usernames which are located on Sheet2.
And then trying to fill empty cells with those usernames in Sheet3 in
a range of cells: A1 to I9. Below is my code. But seems like I am not
understanding how to use activecell and so Excel doesn't like it
giving an error saying "Object doesn't support this property or
method".

Appreciate for a help in advance!

Thanks,
DD

Private Sub CommandButton1_Click()

Dim oldnames As Range
Dim newnames As Range
Dim x As Integer
Dim n As Integer
Dim i As String
Dim k As Integer

Set newnames = Worksheets("Sheet2").Range("A1:A4")

n = 1

For Each cell In Worksheets("Sheet3").Range("A1:I9")
    If IsEmpty(ActiveCell) Then
       Worksheets("Sheet3").ActiveCell = newnames(n)
            If (n <= 2) Then
                n = n + 1
            Else
                n = 1
   End If
   End If
Next cell

UserForm1.Hide

End Sub