1.利用VBA在A列中依次输入1-100的数字:
2.按Alt+F11,输入代码,通过变量i从1到100循环,依次在对应的A列行数为i的单元格中输入数值i
3.按F5执行代码
根据你图一数据及要求写了一段代码,看是不是满足要求:1、运行前请备份原始数据表,切记!
2、为了便于检查,会自动在相关单元格写入公式。
3、代码直接放到图一的“水准测量记录”表的代码页中,如果写在模块中,请自行修改代码。代码运行后会生成图二的样子,只能运行一次,再次运行时必须保证数据格式是图一(原表)的格式。由于有几个数据是随机数,多次测试结果会不同。
4、默认图一E列是已知数据。
Sub 插入数据()
Dim i As Long
Dim k As Long
Dim n As Long
n = 8
k = 10
With ActiveSheet
For i = 11 To 65536
If .Cells(k, 4).Value - .Cells(i, 5).Value >= 0.2 And .Cells(k, 4).Value - .Cells(i, 5).Value <= 4.8 Then
.Range("C" &i) = "=$D$" &k &"-E" &i
Else
If .Cells(k, 4).Value - .Cells(i, 5).Value >= 4.8 Then
.Rows(i).Insert Shift:=xlDown
.Range("A" &i) = "ZD" &n + 1
.Range("C" &i) = "=Round(rand() * (4.8 - 4.2) + 4.2, 3)"
.Range("E" &i) = "=$D$" &k &"-C" &i
.Rows(i + 1).Insert Shift:=xlDown
.Range("B" &i + 1) = "=Round(rand() * (0.5 - 0.2) + 0.2, 3)"
.Range("D" &i + 1) = "=E" &i &"+B" &i + 1
ElseIf .Cells(k, 4).Value - .Cells(i, 5).Value <= 0.2 Then
.Rows(i).Insert Shift:=xlDown
.Range("A" &i) = "ZD" &n + 1
.Range("C" &i) = "=Round(rand() * (0.5 - 0.2) + 0.2, 3)"
.Range("E" &i) = "=$D$" &k &"-C" &i
.Rows(i + 1).Insert Shift:=xlDown
.Range("B" &i + 1) = "=Round(rand() * (4.8 - 4.2) + 4.2, 3)"
.Range("D" &i + 1) = "=E" &i &"+B" &i + 1
End If
k = i + 1
n = n + 1
i = i + 1
End If
If .Range("E" &i + 1) = "" Then Exit For
Next i
End With
End Sub
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)