CODEWITHSUNDEEP
vbaexcel
Lamont Cranston
Lamont Cranston

Reputation: 15

Inserting single values within cell range using button

I'm attempting to use VBA to insert a name into a small cell range (6 cells horizontally) with the following conditions in mind:

I've managed to achieve some of this using the following code, but it is a little difficult since a cell is randomly selected within the range (sometimes have to click multiple times for the name to show up):

Sub button_onclick()

Dim rCells As Range
Set rCells = Range("N3:S3")

Dim rRandom As Long
    rRandom = Int(Rnd * rCells.Cells.Count) + 1

With rCells.Cells(rRandom)
    If IsEmpty(rCells.Cells(rRandom)) Then
        .Value = "Kent Allard"
    End If
End With

End Sub

Upvotes: 1

Views: 43

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

The question is a bit unclear, so the answer is kind of generic: in order to achieve the goal as described, you can modify your Sub as shown below:

Sub button_onclick()
    Dim rCells As Range
    Set rCells = Range("N3:S3")

    Dim rRandom As Long
        rRandom = Int(Rnd * rCells.Cells.Count) + 1

    With rCells.Cells(rRandom)
        If IsEmpty(rCells.Cells(rRandom)) Then
            .Value = "Kent Allard"
        Else
            .Value = ""
        End If
    End With
End Sub

Pertinent to your modified requirements (Cells populated in sequential order instead of random, the last cell to clear if it's not empty), refer to the following code snippet:

Sub button_onclick()
    Dim rCells As Range
    Dim I As Integer, max As Integer

    Set rCells = Range("N3:S3")
    max = Range("S3").Column - Range("N3").Column + 1

    For I = 1 To max
        With rCells.Cells(I)
            If IsEmpty(rCells.Cells(I)) Then
                .Value = "Kent Allard"
                Exit For
            Else
                If I = max Then .Value = ""
            End If
        End With
    Next I
End Sub

Hope this may help.

Upvotes: 1

LMM9790
LMM9790

Reputation: 486

I am not entirely sure what you are trying to achieve. As far as I understand it, the following should achieve the task.

    Sub CommandButton1_Click()
    Dim rCells As Range
    Dim rRandom As Long
    Dim intFilledCells As Integer

    Set rCells = Range("N3:S3")

    rRandom = Int(Rnd * 21) + 1

    ' You could achieve the placement of a random name by making a list
    ' of the 21 names (here it is supposed they are written in the cells A1:A21

   intFilledCells = WorksheetFunction.CountA(rCells)

    If intFilledCells <= 5 Then
        Range("N3").Offset(0, intFilledCells).Value = Range("A" & rRandom).Value
    Else
        Range("N3").Offset(0, intFilledCells - 1).Value = ""
    End If

End Sub

Upvotes: 1

Related Questions