Excel 2007 VBA学习(一)

Excel 2007 VBA学习(一)

 

Range.Select方法的使用

Range.Select方法主要用于选择对象,其语法格式为:表达示.Select,其中表达式是指一个Range对象

 

的变量.要选择单元格或单元格区域,使用Select.

 

如:Range(“A1”).Select              Range(“A1:C3”).Select

 

Range.CurrentRegion属性的用法

返回活动单元格所在的周围由空行和空列组成的单元格区域(即通常所说的当前区域)

 

保护所有工作表

 

For Each one In Worksheets

    one.Select

    ActiveSheet.Protect Password:="qwe", DrawingObjects:=True, Contents:=True, Scenarios:=True

Next one

 

撤销保护工作表

For Each one In Worksheets

    one.Select

    ActiveSheet.Unprotect Password:="qwe"

Next one

 

将将选定区域内的数据写入到另一个单元格中

 

Sub writeFex()

Dim myrange As Range

Dim count As Integer

Dim index() As Integer

Dim i As Integer

 

Set myrange = Selection

count = myrange.count

ReDim index(count)

i = 1

 

For Each one In myrange

 index(i) = Application.WorksheetFunction.Rank(one.Value, myrange)

 i = i + 1

 Next one

 

 For i = 3 To count + 2

 ActiveSheet.Cells(i, 10) = index(i - 2)

 Next i

 

' Dim list As String

 

' For i = 1 To 10

'  For j = 1 To count

'    If Cells(j + 2, 10) = i Then

'       list = list & CStr(Cells(j + 2, 2)) & ":" & CStr(myrange(j).Value) & Chr(10)

'    End If

   

''  Next j

' Next i

 '   MsgBox "" & Chr(10) & (list)

End Sub