|
1.單行批量賦值 （1）將數(shù)值常量賦值給單元格,，可以用array函數(shù) Range("A1:D1") = Array(1, 2, 3, 4) 也可以用這種形式 Range("A4:D4") = [{1,2,3,4}] 這種形式實(shí)際上是Evaluate函數(shù)的縮寫； 還有這種形式 Range("A1:D1") = Split("1,2,3,4", ",") 注意：這種方法賦值進(jìn)去的是文本型數(shù)值,； （2）將文本常量賦值給單元格,，和數(shù)值常量賦值給單元格一樣 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函數(shù) Range("A2:D2") = Array(S1, S2, S3, S4) （S1,，S2,，S3，S4在這里是聲明并賦值的變量） 同樣可以用Evaluate函數(shù),，但是不能用縮寫形式,，而且需要先把需要處理的內(nèi)容寫成字符串的形式 s = "{" & S1 & "," & S2 & "," & S3 & "," & S4 & "}" Range("A5:D5") = Evaluate(s) 2.單列的批量賦值 （1）array函數(shù)法，不過需要用到transpose函數(shù)轉(zhuǎn)換一下,； Range("A8:A11") = Application.Transpose(Array(1, 2, 3, 4)) '注：數(shù)組里面的1,2,3,4是數(shù)值,； Range("B8:B11") = Application.Transpose(Array(S1, S2, S3, S4)) '注：數(shù)組里面的S1,S2,S3,S4是變量； Range("C8:C11") = Application.Transpose(Array("S1", "S2", "S3", "S4")) '注：數(shù)組里面的"S1","S2","S3","S4"是文本,； （2）用Evaluate函數(shù)或其縮寫的形式,，不過要注意，逗號需要變成分號； 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函數(shù) 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 & "}" 的實(shí)際內(nèi)容是 "{"S1",1;"S2",2;"S3",3;"S4",4}" 不過在寫字符串的時(shí)候,，雙引號外面要加一層雙引號，和變量連接要用&符號 示例代碼,，請下載 |
|
|
