一些常用的宏脚本

作者：佚名 | 来源：网络 | 添加时间：2008-04-25 21:36:09 | 人气：185

一些常用的宏脚本

一、把Excel文件的横列进行转换的宏
/**
* 把Excel文件的横列进行转换的宏
*/
Sub RowsColsTransform()

Dim ColumnCount As Integer
Dim RowCount As Integer
Dim DestSheetName As String
Dim SourceSheetName As String
Dim SrcRowCount As Integer
Dim SrcColCount As Integer

Dim StepRow As Integer
Dim StepCol As Integer

StepRow = 0
StepCol = 0

' Dim CellValue As String
DestSheetName = "Sheet3"
SourceSheetName = "Sheet0"

Sheets(SourceSheetName).Select
SrcRowCount = 52
' Selection.Rows.Count
SrcColCount = 8

' Selection.Columns.Count

' 循环选择的每一行。
For RowCount = 1 To SrcRowCount
        ' 循环选择的每一列。
        Sheets(SourceSheetName).Select
        If Cells(RowCount, 1) <> emptystring Then

            For ColumnCount = 1 To SrcColCount

                Sheets(SourceSheetName).Select
                CellValue = Cells(RowCount, ColumnCount)

                Sheets(DestSheetName).Select

                Cells(ColumnCount + StepRow, (RowCount - StepCol)) = CellValue
            ' 开始 ColumnCount 循环的下一个迭代。
            Next ColumnCount
        Else
            StepCol = RowCount
            StepRow = StepRow + SrcColCount
        End If

    ' 开始 RowCount 循环的下一个迭代。
    Next RowCount
End Sub

二、自动加上链接的宏

Sub href_link()
    Dim s As String
    Dim max As Integer
    Dim selectCols As Integer
    Dim begin As Integer
    '需要处理的行数
    max = 12728
    '选择的列
    selectCols=1
    Worksheets(1).Select
    For begin = 1 To max
         Cells(begin,selectCols).Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
                "" + Cells(begin, selectCols)
    Next begin

End Sub

责任编辑：冬天来了