13 数组
注意,一维数组只能横向输出,不能竖向输出,如range(“A4:F4”)=arr 这样可以,但是A4:A8却不可以
'定义一维数组并赋值
Sub test()
Dim arr(1 To 4)
arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"
End Sub
'定义二维数组并赋值
Sub test1()
Dim arr(1 To 4, 1 To 2)
arr(1, 1) = "张三"
arr(1, 2) = 30
arr(2, 1) = "李四"
arr(2, 2) = 35
arr(3, 1) = "王五"
arr(3, 2) = 40
End Sub
'将数组中的某个值输出到单元格
Sub test3()
Dim arr(1 To 4)
arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"
Range("b2") = arr(2)
End Sub
'将一维数组中的所有值输出到单元格区域
Sub test4()
Dim arr(1 To 4)
arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"
Range("a7:d7") = arr
End Sub
'将二维数组中的所有值输出到单元格区域
Sub test5()
Dim arr(1 To 3, 1 To 2)
arr(1, 1) = "张三"
arr(1, 2) = 30
arr(2, 1) = "李四"
arr(2, 2) = 35
arr(3, 1) = "王五"
arr(3, 2) = 40
Range("a15:b17") = arr
End Sub
'将区域赋值给数组
Sub test6()
Dim arr()
arr = Range("a1:a5")
End Sub
Sub test()
Dim i, k
Dim t
Dim str As String
Dim arr()’
t = Timer
arr = Range("g1:j200000") ‘括号里面有双引号
str = Range("n5")
For i = 2 To 200000
If arr(i, 1) = str Then
k = k + arr(i, 4)
End If
Next
Range("p5") = k
MsgBox Timer – t 注意msgbox后面直接写就好了,不用任何符号
End Sub
找销量最好的产品
Sub sz()
Dim arr()
ReDim arr(1 To 6)(注意REDIM 重新定义)
Fori = 1 To 6
arr(i) = Range("b" & i + 1) *
Range("c" & i + 1) (注意给数组赋予变量的方法)
如果写arr= Range("b" & i + 1) *
Range("c" & i + 1)没有办法给数组赋值
Next
Range("h3") =Application.WorksheetFunction.Max(arr())
Range("h2") = Range("a"& Application.WorksheetFunction.Match(Range("h3"), arr()) + 1)
End Sub
Sub test1()
Dimi, j, k, l, m As Integer
Dim arr()
arr = Range("a1:a80") (如果写成a2就会出现下标越界错误)要注意给数组赋值的写法
For i = 2 To 80
Fo rj = 2 To 80
For k = 2 To 80
For l = 2 To 80
Ifarr(i, 1) + arr(j, 1) + arr(k, 1) + arr(l, 1) = "124704" Then
GoTo 100 (如果多重for 循环,这个是个非常不错的跳出循环的方法)
EndIf
Next
Next
Next
Next
100
Range("b4") = Range("a"& i)
Range("b5") = Range("a"& j)
Range("b6") = Range("a"& k)
Range("b7") = Range("a"& l)
End Sub )