VBA值列选取与复制,赋值.doc

从活动单元格向上12行到向上1行的总和ActiveCell.FormulaR1C1=”SUM(R-12)C:R-1C”引用第C-D列和第F-G列Range(“B2:D6”)引用B2-D6的单元格同Range(“B2”,”D6”) ,Rangge(Cells(2,2),Cells(6,4)引用第2行3列Cells(2,3)或Cells(2,”C”)ActiveCell.Resize(4,4)自当前单元格开始创建一个4行4列的区域Range(“B2”).Resize(5,3)从B2开始扩展的区域为5行3列的区域不连续单元格的引用Range(“A1:B2,C4,D6:F7”)引用从A1-B2、C4和D6-F7的区域设置多个不连续区域Sub UnionDim bigRange as RangeWorkSheets(“Sheet1”).ActivateSet bigRange=Application.Union(Range(“A1:B2”),Range(“C4”),Range(“D6:F7”)bigRange.SelectEnd Sub新建名称为单元格区域添加名称”客户”Sub AddName2()ActiveSheet.Names.Add Name:=”客户”,RefersTo:=”=” & Selections.Address()End Sub直接为选定的区域命名Selection.Name=”品名”新建名称Sub AddName1()ActiveSheet.Names.Add Name:=”品名”,RefersTo:=”=”=$B$2:$B$80End Sub删除命名Sub DeleteRangeNames()Dim rName as NameFor Each rName In ActiveWorkbook.NamesrName.DeleteNext rNameEnd Sub选取单个单元格Range(“A1”).SelectCells(1,1).SelectA1.Select选取A1-A10,C1-C10的不连续区域Range(“A1:A10,C1:C10”).SelectUnion(Range(“A1:A10”),Range(“C1:C10”).Select选取当前区域和使用的区域CurrentRegion.SelectionUsedRange.Selection选取A1-B10的单元格区域Range(“A1:B10”).SelectRange(Cells(1,1),Cells(10,2).Select命名区域的选择Range(“品名”).Select选取整个工作表Cells.Select或Columns.Select或Rows.Select使用常量给单元格赋值Sub ResetValuesToZero2()Dim n as RangeFor Each n In Worksheets(“Sheet1”).Range(“WorkArea1”)If n.Value0 Thenn.Value=0End IfNext nEnd Sub给一个区域赋值Sub setZero()Sheet1.Range(“A1:D5”)=0End Sub把变量赋值给单元格Sub test()For i=1 to 10Range(“A” & i)=iNext iEnd Sub使用Chr函数转换数值变量为字符Sub Test2()Dim a as StringDim I as IntegerFor i=65 to 70A=Chr(i)Range(a & 1)=iNext iEnd Sub把单元格的值赋值给数组Sub RangeToArray()Dim myArray(3) as integerDim I as integerFor i=1 to 3myArray(i)=Cells(1,i)Next iEnd Sub把单元格中一行单元格的值赋给数组Sub RangeToArray0()Dim I as integerDim varArray as VariantvarArray=Sheet1.Range(“A1:C1”).ValueFor i=1 to 3MsgBox varArray(1,i)Next iEnd Sub把一个单元格矩形区域的值赋给数组Sub RangeToArray1()Dim varArray as VariantDim r%Dim c%varArray=Sheet1.Range(“A1:C3”).Value按行循环数组For r=1 to UBound(varArray,1)For c=1 to UBound(varArray,2)Debug.Print varArray(r,c)Next cNext rEnd Sub把数组的值赋给单元格Sub ArrayToRange2()Dim aData(2,2) as VariantDim RowCnt As integer,ColCnt as integeraData(0,0)=1aData(0,1)=2aData(0,2)=3aData(1,0)=4aData(1,1)=5aData(1,2)=6aData(2,0=7aData(2,1)8aData(2,2)9End Sub把公式传给数组Sub RangeToArray4()Dim arr As VariantArr=a6:c10.Formulae6:g10=arrEnd Sub单元格间的赋值Sub cells()Dim I as IntegerFor i=1 to 5Cells(i+5,7)=Cells(I,1)Next iEnd Sub把一行单元格赋值给一列单元格Sub Range2()Dim I as IntegerDim j as integerFor i=1 to 5J=j+1Cells(I,10)=cells(2,j)Next iEnd Sub使用数组转置实现行、列单元格之间的数据传递Sub range3()Dim varArray as VaraintvarArray=Application.Transpose(A1:E1)G1:G5=varArrayEnd Sub按地址拆分工作表()Sub按地址拆分工作表()On Error Resume NextDim n As Integer, K As IntegerDim j As Longn = 2获取A列从下数不为空的单元格行号j = 10For K = 2 To jDo Until Left(Sheet2.Cells(K, 2), 3) Left(Sheet2.Cells(K + 1, 2), 3)k中保存省份名称相同的地址数目K = K + 1Loop判断第2列K行的地区省份与第2列K+1行的地区省份是否相同If Left(Sheet2.Cells(K, 2), 3) Left(Sheet2.Cells(K + 1, 2), 3) Then如果不同复制Sheet2中的指定单元格Sheet2.ActivateRange(Cells(n, 1), Cells(K, 2).Copy添加工作表Sheets.A