VLOOKUP vba中的参考变量工作表 [英] Reference variable worksheet in VLOOKUP vba

问题描述

我正在编写一个基于现有工作表上的值创建可变工作表的宏。我管理的那部分很好，但现在我需要在引用新创建的工作表的另一个工作表上添加一个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 
 
带表格（部件类型REC）
如果.AutoFilterMode = True那么.AutoFilterMode = False 
 
 .Range（D1：D& LastRow）.AdvancedFilter Action：= xlFilterCopy，CopyToRange：=。Range（J1），Unique：= True 
 
设置rngCriteria = .Range（J1）CurrentRegion 
 
对于I = 2到.Cells（Rows.Count，J）。End（xlUp）.Row 
 sName = .Cells（I，J）
设置ws = ThisWorkbook.Worksheets.Add 
 ws.Name = sName 
 .Range（D1：D& LastRow）.AutoFilter字段：= 1，Criteria1：==& .Cells（I，J）。值
 .Range（A1：H& LastRow）.SpecialCells（xlCellTypeVisible）.Copy目的地：= ws.Range（A1）
下一步I 
 
 .AutoFilterMode = False 
结束
表单（部件类型REC）。选择
列（J：J）。选择
 Selection.ClearContents 
范围（A1）。选择
  

这里是VLOOKUP我需要参考新的工作表：

 表格（TP零件）选择
范围（O2 ）。选择
 Application.CutCopyMode = False 
 ActiveCell.FormulaR1C1 == VLOOKUP（RC [-1]，'ws.name'！C [-14]，1，FALSE）
 Range（O2）。选择
  

我在哪里出错？

提前感谢

解决方案

尝试这个（ UNTESTED - Just在这里输入

  Range（O2）。FormulaR1C1 == VLOOKUP（RC [-1] ，& ws.name&！C [-14]，1，FALSE）
  

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:

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:

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!

解决方案

Try this (UNTESTED - Just typed it here)

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1]," & ws.name & "!C[-14],1,FALSE)"

这篇关于VLOOKUP vba中的参考变量工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！