1.单行批量赋值
(1)将数值常量赋值给单元格,可以用array函数
Range("A1:D1") = Array(1, 2, 3, 4)
也可以用这种形式
Range("A4:D4") = [{1,2,3,4}]
这种形式实际上是Evaluate函数的缩写;
还有这种形式
Range("A1:D1") = Split("1,2,3,4", ",") 注意:这种方法赋值进去的是文本型数值;
(2)将文本常量赋值给单元格,和数值常量赋值给单元格一样
Range("A3:D3") = Array("S1", "S2", "S3", "S4")
Range("A6:D6") = [{"S1","S2","S3","S4"}]
Range("A1:D1") = Split("S1,S2,S3,S4", ",")
(3)将变量赋值给单元格,可以用array函数
Range("A2:D2") = Array(S1, S2, S3, S4) (S1,S2,S3,S4在这里是声明并赋值的变量)
同样可以用Evaluate函数,但是不能用缩写形式,而且需要先把需要处理的内容写成字符串的形式
s = "{" & S1 & "," & S2 & "," & S3 & "," & S4 & "}"
Range("A5:D5") = Evaluate(s)
2.单列的批量赋值
(1)array函数法,不过需要用到transpose函数转换一下;
Range("A8:A11") = Application.Transpose(Array(1, 2, 3, 4)) '注:数组里面的1,2,3,4是数值;
Range("B8:B11") = Application.Transpose(Array(S1, S2, S3, S4)) '注:数组里面的S1,S2,S3,S4是变量;
Range("C8:C11") = Application.Transpose(Array("S1", "S2", "S3", "S4")) '注:数组里面的"S1","S2","S3","S4"是文本;
(2)用Evaluate函数或其缩写的形式,不过要注意,逗号需要变成分号;
Range("D8:D11") = [{1;2;3;4}]
s = "{" & S1 & ";" & S2 & ";" & S3 & ";" & S4 & "}"
Range("E8:E11") = Evaluate(s)
Range("F8:F11") = [{"S1","S2","S3","S4"}]
(3)用Split函数
Range("C8:C11") = Application.Transpose(Split("S1,S2,S3,S4", ","))
3.多行多列的批量赋值(这里以四行两列为例)
只能用Evaluate方法完成,而且不能用缩写形式
s = "{""S1""," & S1 & ";""S2""," & S2 & ";""S3""," & S3 & ";""S4""," & S4 & "}"
Range("A13:B16") = Evaluate(s)
注:字符串s = "{""S1""," & S1 & ";""S2""," & S2 & ";""S3""," & S3 & ";""S4""," & S4 & "}"
的实际内容是 "{"S1",1;"S2",2;"S3",3;"S4",4}"
不过在写字符串的时候,双引号外面要加一层双引号,和变量连接要用&符号
示例代码,请下载