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

‘从活动单元格向上12行到向上1行的总和 ActiveCell.FormulaR1C1=”SUM(R[-12])C:R[-1]C” ‘引用第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 Union ???????Dim bigRange as Range ???????WorkSheets(“Sheet1”).Activate ???????Set bigRange=Application.Union(Range(“A1:B2”),Range(“C4”),Range(“D6:F7”)) ???????bigRange.Select End Sub ? ‘新建名称 为单元格区域添加名称”客户” Sub AddName2() ???????ActiveSheet.Names.Add Name:=”客户”,RefersTo:=”=” Selections.Address() End Sub ? ‘直接为选定的区域命名 Selection.Name=”品名” ? ‘新建名称 Sub AddName1() ???????ActiveSheet.Names.Add Name:=”品名”,RefersTo:=”=”=$B$2:$B$80 End Sub ? ‘删除命名 Sub DeleteRangeNames() ???????Dim rName as Name ???????For Each rName In ActiveWorkbook.Names ??????????????rName.Delete ???????Next rName End Sub ? ‘选取单个单元格 Range(“A1”).Select Cells(1,1).Select [A1].Select ? ‘选取A1-A10,C1-C10的不连续区域 Range(“A1:A10,C1:C10”).Select Union(Range(“A1:A10”),Range(“C1:C10”).Select ? ‘选取当前区域和使用的区域 CurrentRegion.Selection UsedRange.Selection ? ‘选取A1-B10的单元格区域 Range(“A1:B10”).Select Range(Cells(1,1),Cells(10,2)).Select ? ‘命名区域的选择 Range(“品名”).Select ? ‘选取整个工作表 Cells.Select或Columns.Select或Rows.Select ? ‘使用常量给单元格赋值 Sub ResetValuesToZero2() ???????Dim n as Range ???????For Each n In Worksheets(“Sheet1”).Range(“WorkArea1”) ???????If n.Value0 Then ??????????????n.Value=0 ???????End If ???????Next n End Sub ? ‘给一个区域赋值 Sub setZero() ???????Sheet1.Range(“A1:D5”)=0 End Sub ? ‘把变量赋值给单元格 Sub test() ???????For i=1 to 10 ??????????????Range(“A” i)=i ???????Next i End Sub ? ‘使用Chr函数转换数值变量为字符 Sub Test2() ???????Dim a as String ???????Dim I as Integer ???????For i=65 to 70 ??????????????A=Chr(i) ??????????????Range(a 1)=i ???????Next i End Sub ? ‘把单元格的值赋值给数组 Sub RangeToArray() ???????Dim myArray(3) as integer ???????Dim I as integer ???????For i=1 to 3 ??????????????myA