返回特定工作表的父工作簿范围

Question

Sub Vlkuprangcall()
Dim strColumn As String
Dim Rg As Range
Dim wb2 As Workbook

Set wb2 = Workbooks.Open("C:\Users\ashokkumar.d\Desktop\Test\IC Raphael\Janalakshmi\MIS\MIS.xlsx")
Set Rg = wb2.Sheets(3).Range("A3:Z10000")

Application.Workbooks(2).Activate
With ActiveSheet
    a = ActiveCell.Column
    lastrow = 100
    strColumn = Split(ActiveCell.Address, "$")(1)
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-15]," & Rg.Parent.Parent.Name & "!" & Rg.Address(ReferenceStyle:=xlR1C1) & ",6,False)"
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Range(strColumn & lastrow))

end Sub

Answer 1

您可以使用address，第4个参数是XLExterner，它将添加Worksheet对象的名称和Workbook对象的名称。

因此formular1c1行应为：

ActiveCell.FormulaR1C1 = "=vlookup(RC[-15]," & Rg.Address(True, True, xlR1C1, xlExternal) & ",6,False)"

注意：应尽量避免使用Active、ActiveSheet和ActiveCell，而应使用完全限定的Range和Cells对象。