ExcelVBA 批量生成工作表并随机生成成绩表
ExcelVBA 批量生成工作表并随机生成成绩表
ExcelVBA 批量生成工作表并随机生成成绩表
'有时候我的制作教程的过程中需要一些随机的数据，如成绩表，有时也需要很多表，不想一个一个建立工作表，所以为了方便
'制作了一个随机生成工作表，并生成成绩单，姓名随机，分数随机，部分是前面的所有成绩的部分，名次是部分成绩的排名
Sub 批量添加工作表生成成绩单表()
Dim brr(), to_num, col_num, Rnd_num
Dim sht As Worksheet
Dim ss As Worksheet
Application.DisplayAlerts = False
For Each ss In Sheets
If ss.Name <> "Sheet1" Then ss.Delete
Next
With Worksheets("Sheet1")
arr = Range("a1:b" & Range("a1").End(xlDown).Row)
to_num = UBound(arr, 1)
End With
title_arr = Array("序号", "班别", "姓名", "语文", "数学", "英语", "政治", "物理", "化学", "地理", "历史", "总分", "名次")
col_num = UBound(title_arr) + 1
' MsgBox col_num
m = 1
For i = 1 To 20
Randomize
Rnd_num = Int(15 * Rnd + 1)
ReDim brr(1 To Rnd_num, 1 To col_num)
For ii = 1 To UBound(brr, 1)
brr(ii, 1) = ii
brr(ii, 2) = arr(Int(to_num * Rnd + 1), 2) 'arr(Application.RandBetween(2, UBound(arr, 1)), 2)
For jj = 3 To UBound(brr, 2) - 1
brr(ii, jj) = Int(100 * Rnd + 1) 'Application.RandBetween(10, 100)
Next jj
Next ii
Set sht = Sheets.Add(After:=Sheets(Sheets.Count))
sht.Name = "S-" & i
With sht
Range("a1").Resize(, col_num) = title_arr
Range("a2").Resize(UBound(brr, 1), UBound(brr, 2)) = brr
Range(Cells(2, col_num - 1), Cells(UBound(brr, 1) + 1, col_num - 1)).FormulaR1C1 = "=SUM(RC[-9]:RC[-1])"
Range(Cells(2, col_num), Cells(UBound(brr, 1) + 1, col_num)).FormulaR1C1 = "=RANK(RC[-1],R2C[-1]:R" & UBound(brr, 1) + 1 & "C[-1])"
.UsedRange.Borders.LineStyle = 1
.UsedRange.HorizontalAlignment = xlCenter
End With
Next
Application.DisplayAlerts = True
End Sub
今天重新学习的内容有
1.删除工作表，
2.建立工作数
3.数组，并Redim
4.单元格的FormulaR1C1的格式，用输入了“=SUM()”与”=RANK()”公式
5.随机生成整数
