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}" 不过在写字符串的时候,双引号外面要加一层双引号,和变量连接要用&符号 示例代码,请下载 |
|