Populating a range of empty cells with usernames

  • Thread starter Thread starter DD
  • Start date Start date
D

DD

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
 
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
 
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
Click to expand...

Thank you Paul! I copied and tried the code that you outlined, but
running into the same run time error 438 saying "Object doesn't
support this property or method" for the line of code:

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

Can I try something to work around it?

Thanks,
DD
 
Hi
I've cleared out all the activate stuff until the last line. In
general, activating anything is a nuisance and only needs to be done
to display sheet3 when the macro finishes. What is left is the sub
below.
What it does is look in A1 on sheet 3. If A1 is empty then the value
of A1 on sheet 2 is inserted and n is set to 2. Then it looks in B1 on
sheet 3 (for...next in a range searches horzontally to the end of the
row then on to the next row etc). If B1 is empty and n = 2 then the
value of A2 on sheet 2 is inserted and n = 3. If n = 1 then the value
of A1 on sheet 2 is inserted and n = 2. Then it looks in C1 on sheet
3. If C3 is empty and n = 3 (i.e. A1 and B1 were empty and were then
filled) then C3 will be left blank. And so on.
If the range A1:I9 was originally blank then after the macro runs you
will see column A filled with A1 from sheet 2, column B filled with A2
from sheet 2 and column C blank. Columns D,E and F will repeat this
pattern and so on. I doubt this is what you want but you may be able
to edit the sub below easily enough.

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
For Each myCell In Worksheets("Sheet3").Range("A1:I9")
If Trim(myCell.Value) = "" Then
If (n <= 2) Then
myCell.Value = newnames.Cells(n, 1).Value
n = n + 1
Else
n = 1
End If
End If
Next myCell
Worksheets("Sheet3").Activate

End sub

regards
Paul
 
Hi
I've cleared out all the activate stuff until the last line. In
general, activating anything is a nuisance and only needs to be done
to display sheet3 when the macro finishes. What is left is the sub
below.
What it does is look in A1 on sheet 3. If A1 is empty then the value
of A1 on sheet 2 is inserted and n is set to 2. Then it looks in B1 on
sheet 3 (for...next in a range searches horzontally to the end of the
row then on to the next row etc). If B1 is empty and n = 2 then the
value of A2 on sheet 2 is inserted and n = 3. If n = 1 then the value
of A1 on sheet 2 is inserted and n = 2. Then it looks in C1 on sheet
3. If C3 is empty and n = 3 (i.e. A1 and B1 were empty and were then
filled) then C3 will be left blank. And so on.
If the range A1:I9 was originally blank then after the macro runs you
will see column A filled with A1 from sheet 2, column B filled with A2
from sheet 2 and column C blank. Columns D,E and F will repeat this
pattern and so on. I doubt this is what you want but you may be able
to edit the sub below easily enough.

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
For Each myCell In Worksheets("Sheet3").Range("A1:I9")
   If Trim(myCell.Value) = "" Then
   If (n <= 2) Then
       myCell.Value = newnames.Cells(n, 1).Value
       n = n + 1
   Else
       n = 1
   End If
End If
Next myCell
Worksheets("Sheet3").Activate

End sub

regards
Paul
Click to expand...

Thanks Paul! That worked perfectly...

DD
 
Back
Top