VLOOKUP vba 中的引用变量工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10142726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reference variable worksheet in VLOOKUP vba
提问by user955289
I am writing a macro that creates variable worksheets based on a value on an existing worksheet. I managed that part fine, but now I need to add a VLOOKUP formula on another sheet that references the newly created sheets. There is no set pattern to the name of the new worksheets, so I having trouble referencing them. Here is the code I used to create the new worksheets:
我正在编写一个宏，它根据现有工作表上的值创建可变工作表。我很好地管理了这部分，但现在我需要在另一个引用新创建的工作表的工作表上添加一个 VLOOKUP 公式。新工作表的名称没有固定模式，因此我无法引用它们。这是我用来创建新工作表的代码：
Dim ws As Worksheet
Dim rngCriteria As Range
Dim sName As String
Dim I As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Part Type REC")
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("D1:D" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("J1"), Unique:=True
Set rngCriteria = .Range("J1").CurrentRegion
For I = 2 To .Cells(Rows.Count, "J").End(xlUp).Row
sName = .Cells(I, "J")
Set ws = ThisWorkbook.Worksheets.Add
ws.Name = sName
.Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:="=" & .Cells(I, "J").Value
.Range("A1:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")
Next I
.AutoFilterMode = False
End With
Sheets("Part Type REC").Select
Columns("J:J").Select
Selection.ClearContents
Range("A1").Select
And here is the VLOOKUP that I need to reference the new worksheets:
这是我需要引用新工作表的 VLOOKUP：
Sheets("TP Parts").Select
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'ws.name'!C[-14],1,FALSE)"
Range("O2").Select
Where am I going wrong with this?
我哪里出错了？
Thanks in advance!
提前致谢！
回答by Siddharth Rout
Try this (UNTESTED - Just typed it here)
试试这个（未经测试 - 只是在这里输入）
Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1]," & ws.name & "!C[-14],1,FALSE)"
