|
‘从活动单元格向上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.Value<>0 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 myArray(i)=Cells(1,i) Next i End Sub ‘把单元格中一行单元格的值赋给数组 Sub RangeToArray0() Dim I as integer Dim varArray as Variant varArray=Sheet1.Range(“A1:C1”).Value For i=1 to 3 MsgBox varArray(1,i) Next i End Sub ‘把一个单元格矩形区域的值赋给数组 Sub RangeToArray1() Dim varArray as Variant Dim 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 c Next r End Sub ‘把数组的值赋给单元格 Sub ArrayToRange2() Dim aData(2,2) as Variant Dim RowCnt As integer,ColCnt as integer aData(0,0)=1 aData(0,1)=2 aData(0,2)=3 aData(1,0)=4 aData(1,1)=5 aData(1,2)=6 aData(2,0=7 aData(2,1)8 aData(2,2)9 End Sub ‘把公式传给数组 Sub RangeToArray4() Dim arr As Variant Arr=[a6:c10].Formula [e6:g10=arr End Sub ‘单元格间的赋值 Sub cells() Dim I as Integer For i=1 to 5 Cells(i+5,7)=Cells(I,1) Next i End Sub ‘把一行单元格赋值给一列单元格 Sub Range2() Dim I as Integer Dim j as integer For i=1 to 5 J=j+1 Cells(I,10)=cells(2,j) Next i End Sub ‘使用数组转置实现行、列单元格之间的数据传递 Sub range3() Dim varArray as Varaint varArray=Application.Transpose([A1:E1]) [G1:G5=varArray End Sub ‘按地址拆分工作表() Sub 按地址拆分工作表() On Error Resume Next Dim n As Integer, K As Integer Dim j As Long n = 2 '获取A列从下数不为空的单元格行号 j = 10 For K = 2 To j Do Until Left(Sheet2.Cells(K, 2), 3) <> Left(Sheet2.Cells(K + 1, 2), 3) 'k中保存省份名称相同的地址数目 K = K + 1 Loop '判断第2列K行的地区省份与第2列K+1行的地区省份是否相同 If Left(Sheet2.Cells(K, 2), 3) <> Left(Sheet2.Cells(K + 1, 2), 3) Then '如果不同复制Sheet2中的指定单元格 Sheet2.Activate Range(Cells(n, 1), Cells(K, 2)).Copy '添加工作表 Sheets.Add '为工作表命名，其名称取自Sheet2工作表中第二列的前三个汉字 ActiveSheet.Name = Left(Sheet2.Cells(K, 2), 3) '把复制的内容粘贴到新建的工作表中 Range("A2").Select ActiveSheet.Paste End If n = K + 1 Next K End Sub
EXCEL的内容分别根据地址生成新工作表，并填入内容 ‘单元格之间的复制 ‘把当前区域的数据复制到另一区域 Sub CopyCurrentRegion() Sheets(“sheet1).Range(“A1”).CurrentRegion Copy Sheets(“sheet2”).Range(“A1”) End Su ‘将Sheet1的单元格区域C1:C5中的数据复制到单元格区域D1:D5中 Sub CopyPaste() WorkSheets(“Sheet1”.Range(“C1:C5”).Copy ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(“D1:D5) End Sub |
|
|
