0 初始化

Sub InitialData()

    dt = Sheet5.Range("infoDate").value

    pathstring = ThisWorkbook.Path & "\"

    dataPath = pathstring & "Risk \"

    cibPath = pathstring & "Risk\cib_3.1.1\"

    riskPath = "F:\1_H\CS\downloads\"

End Sub

 

1 打开源文件

  InvestVal_QD = "InvestVal" & report_date & ".xls"

Workbooks.Open ThisWorkbook.Path & "\Data\" & InvestVal_QD

 

2 单元格定位

nav_row = Application.WorksheetFunction.Match("NAV:", Sheet6.Range("K:K"), False)

Sheet2.Cells(todayRow, Application.WorksheetFunction.Match("资产净值", Sheet2.Range("2:2"), False)).value _

= Sheet6.Cells(nav_row, 13).value

/////////////////////////////////////

Set rng = Sheet12.Range("A65536").End(xlUp)

  Dim rng_row As Long

   rng_row = rng.row

   sh.Range("C8:V8").AutoFill Destination:=sh.Range("C8:V" & rng_row)

 

////////////////////////////////////////

定位行：

Dim todayRow As Long

todayRow = Application.WorksheetFunction.Match(CLng(dt), Sheet2.Range("A:A"), False)

定位列

TodayCol= Application.WorksheetFunction.Match("Cost", Sheet2.Range("2:2"))

定位单元格：

Sheet2.Cells(todayRow,todayCol).value= Sheet6.Cells(1, 5).End(xlDown).End(xlDown).Offset(1, 2).value

 

定位其他workbook的单元格

Workbooks(dailyworkbook).Sheets(1).Activate

Workbooks(dailyworkbook).Sheets(1).Range("B7:c7").Select

 

 

3 数据读取(含循环)

colDes = Array("Invest", "ISIN ", " DATE", "INT RATE", "MATURITY", "BOOK COST")

    For i = 0 To 5

        col(i) = Application.WorksheetFunction.Match(colDes(i), _

                Sheets("InvestVal_QD-CITIC-HVT").Range("7:7"), False)

    Next i

 

4 赋值

即赋值时，可以用cell赋值，也可以用range赋值

Sheet.Cells(x,y).value=sheet.range(m).value

Sheet.Cells(x,y).value=sheet.range(m,n).value

Sheet.range(x).value=sheet.range(m).value

 

5 复制，粘贴

/////////////////////////////////////////////////////////

Sub CopySheet(pathAndName As String, sheetName As Integer, dest As Excel.Worksheet)

    OpenWorkbook pathAndName

        dest.UsedRange.Clear

        Dim wb As Excel.Worksheet

    Set wb = Workbooks(GetFileName(pathAndName)).Sheets(sheetName)

    wb.Cells.Copy

    dest.Range("A1").PasteSpecial Paste:=xlPasteValues

    wb.Parent.Close savechanges:=False

End Sub

 

CopySheet ThisWorkbook.Path & "\Data\" & InvestVal_QD, 1, Sheet6

 

 

 

通常是 range(“”).copy 或者cells(x,y).copy

 

ThisWorkbook.Worksheets("每日汇总").Range("A8:m8").Copy

Workbooks(dailyworkbook).Sheets(1).Range("A7:M7").PasteSpecial Paste:=xlPasteValues

/////////////////////////////////////////////////////////////////////////////////////////////

Workbooks(varbook).Sheets(1).Range("a6:dd100").Copy Destination:= ThisWorkbook.Sheets("var").Range("A1")

 

7 另存为

 

Dim wb As Excel.Workbook

    Set wb = Workbooks.Add

    sh.UsedRange.Copy

    wb.Sheets(1).Paste

    Dim tmp

    tmp = Application.DisplayAlerts

    Application.DisplayAlerts = False

    wb.SaveAs fileName:=dataPath & "RM " & dts & ".rm3D", FileFormat:=xlText

    Application.DisplayAlerts = tmp

    wb.Close False

 

或者是

ThisWorkbook.Worksheets(1).saveas fileName:=dataPath & "RM- " & dts & ".rm3D", FileFormat:=xlText

 

8 运行可执行程序

ub uploadReport()

    Call InitialData

    Dim cmd As String

    cmd = """" & cibPath & "cibRunBatch"" PROD UploadPositions " & _

        Format(dt, "yyyymmdd") & pathstring & "Risk"" """ & dataPath & "RM " & dts & ".rm3D" & """"

    Shell cmd

    ShellAndWait cmd

End Sub

 

9 获得指定行、列中的最后一个非空单元格

#001  Sub LastRow()

#002      Dim rng As Range

#003      Set rng = Sheet1.Range("A65536").End(xlUp)

#004      MsgBox "A列中最后一个非空单元格是" & rng.Address(0, 0) _

#005          & ",行号" & rng.Row & ",数值" & rng.Value

#006      Set rng = Nothing

#007  End Sub

代码解析：

LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。

End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键<End+向上键>、<End+向下键>、<End+向左键>或<End+向右键>，语法如下：

expression.End(Direction)

参数expression是必需的，一个有效的对象。

参数Direction是可选的，所要移动的方向，可以为表格 3‑1 所示的XlDirection 常量之一。

表格 3 ‑1   XlDirection 常量

 

10 常用的excel对象

Workbooks

Workbook

Worksheets

Worksheet

Range

具体属性方法，参加本博客【VBA对象体系】