Private Sub CommandButton1_Click()
Randomize

Dim Y As Integer
'Y:要執行的次數
Y = 5
If Worksheets("工作表2").Cells(Y, 2) = "" Then
  '產生 10 個亂數
  For M = 1 To 10
    Worksheets("工作表1").Cells(M, 1) = Rnd
  Next M
  
  For X = 1 To Y
    If Worksheets("工作表2").Cells(X, 2) = "" Then
      Worksheets("工作表2").Cells(X, 1) = "第 " & X & " 次"
      Worksheets("工作表2").Cells(X, 2) = [max(工作表1!A1:A10)]
      Exit For
    End If
  Next X
Else
  MsgBox "已經執行 " & Y & " 次了," & "若要重新執行,請先清空工作表2的B欄位."
End If

End Sub

google sheets上不能用VBA，你下載開看看是不是你要的

https://drive.google.com/file/d/1VlX6siq7TCcrqayeIek16Kaxxz52akXI/view?usp=sharing

程序部份

Sub testDo()
 '關閉自動更新
 Excel.Application.Calculation = xlCalculationManual
 
 '手動更新
 Calculate
 
 '儲存資料
 Dim data(4) As String
 
 data(0) = Cells(2, 6)
 data(1) = Cells(2, 7)
 data(2) = Cells(2, 8)
 data(3) = Cells(2, 9)
 
 '找空格塞結果
 For i = 0 To 3
    If (data(i) = "") Then
        Cells(2, 6 + i) = Range("C12")
        i = 4
    End If
 Next
 
 '開啟自動更新
 'Excel.Application.Calculation = xlCalculationAutomatic
End Sub

工作表 1 設如下: (原 Formula 不變動)
1.將 Excel "公式" -> "重算選項" 設為 "手動"
2.將 "C9" 命名為"最大值"
3.增加 ActiveX CommandButton 於其上，並命名為 "btnFlush"
4.工作表 1 物件寫入:

Private Sub btnFlush_Click()
    Dim iLastPos As Integer
       
    On Error Resume Next
    iLastPos = Worksheets("工作表2").Columns(2).SpecialCells(xlCellTypeConstants).Count
    On Error GoTo 0
    
    If iLastPos <= 4 Then
        Application.Calculate
        Worksheets("工作表2").Range("B1").Offset(iLastPos).Value = Worksheets("工作表1").Range("最大值").Value
    Else
        If MsgBox("紀錄已滿!" & vbCrLf & "是否要重新記錄? (Y/N)", vbExclamation + vbYesNo, "注意") = vbYes Then
            Worksheets("工作表2").Columns(2).SpecialCells(xlCellTypeConstants).Delete xlShiftUp
            MsgBox "紀錄全部已清空!", vbExclamation + vbOKOnly, "注意"
        End If
    End If
End Sub