Let me start this off by saying I have no prior experience with (ActiveX) buttons in Excel, I know my way around excel but never had to work with VBA that much. I am very open to learning so any nudge in the right direction would be great!

I’m working on an excel workbook that simulates the stock market for a game we’re playing. For comedic effect, these values don’t change predictably but rather are modified using a random number. I have a list of these random numbers and would like to have a button that upon being pressed looks for the bottom-most empty cell in the “Delta” column and pastes a value from the random number list. (see image)Table example

For another part of this workbook, I’ve used the function: OFFSET(C3,COUNTA(C3:C48)-1,0) to find the bottommost filled cell in the “Delta” column. Perhaps this could be re-used?

In order to test the button, I tried to create a more simple version that worked for only one cell.

Private Sub CommandButton1_Click()
    If InStr(1, CommandButton1.Caption, "OFF") Then
        Range("C5").Value = ""
    Else
        Range("C5").Value = Application.WorksheetFunction.Index(Range("Table3[Delta]"), Application.WorksheetFunction.RandBetween(1, Application.WorksheetFunction.CountA(Range("Table3[Delta]"))))
    End If

End Sub

This, however continued to give me “1004” error messages.