vba - Excel VBA Worksheet.Function 与动态工作表引用

Question

我有一个 Excel 文件(wbTL)，其中包含用于分析的主要 WS(INSTRUCTIONS)和许多其他包含每个国家/地区数据的 WS(将在主表中进行分析)
主表有一个包含所有国家的表格，并使用函数显示每个国家的关键信息，并引用相关国家表。

国家 WS 中的信息是从其他 Excel (wb) 发送的。

我的问题 :

假设有一个新国家。
用户将报告来自 (wb) 的信息，并在 (wbTL) 中创建一个具有特定名称的新工作表。

现在我想用新的国家名称更新表，函数应该引用新的工作表生成。

Sub Test()

Dim wb As Workbook, wbTL As Workbook
Dim wsRep As Worksheet, wsIns As Worksheet, wsInsTL As Worksheet
Dim lrowTable As Long, lcolTable As Long
Dim rngTLPath As Range
Dim CountryInList As Single
Dim s As String, j As String
Dim rngTable As Range

Set wb = ThisWorkbook
Set wsIns = wb.Worksheets("INSTRUCTIONS")
Set rngTLPath = wsIns.Range("TLPATH")
Set wbTL = Workbooks.Open(rngTLPath)
Set wsRep = wb.Worksheets("REPORT")
Set wsInsTL = wbTL.Worksheets("INSTRUCTIONS")

lrowTable = wbTL.Worksheets("INSTRUCTIONS").Cells(Rows.Count, 3).End(xlUp).Row
lcolTable = wbTL.Worksheets("INSTRUCTIONS").Cells(6, Columns.Count).End(xlToLeft).Column

Set rngTable = wsInsTL.Range(Cells(5, 3), Cells(lrowTable, lcolTable))
'What I pretend is to storage the variable S as string with the Worksheet for the country.
s = wsIns.Range("A1") 'I make a reference to a cell, lets say it says DENMARK

'在这里我还在努力。我希望代码检查该国家/地区是否已经在表格中。
'如果缺少国家/地区，则添加带有国家/功能的新行。

On Error Resume Next
CountryInList = Application.WorksheetFunction.VLookup(s, rngTable, 1, False)
On Error GoTo 0

If CountryInList = 0 Then
    wsInsTL.Cells(lrowTable + 1, 3).Value = s 'This does not work, leaves cell as Empty

'这是我的主要问题:
'我要做的是引用根据国家名称生成的新WS
'我所期待的是将“s”作为字符串并生成:
=COUNTA('丹麦'!A:A) - 1

    wsInsTL.Cells(lrowTable + 1, 4).Formula = "=Counta('s'!A:A) - 1"
    wsInsTL.Cells(lrowTable + 1, 7).Formula = "=Countif('s'!$H:$H,Vlookup(G$5,OTHER!$N$1:$O$10,2,false))"
End If
End Sub

我试过(添加“s”或双“s”，删除''，用“”或''和''“”创建变量字符串)

有可能做到吗？或者有什么解决方法吗？

提前谢谢了。

Answer 1

使用 Range.Find方法来搜索国家是否存在，而不是 vlookup (无论如何，你写它的方式是行不通的)。另外，看看我是如何将变量包含在 If 中的公式中的。堵塞。

Dim rngC as Range
Set rngC = rngTable.Find(What:=s,lookat:=xlWhole)

If rngC Is Nothing Then

    wsInsTL.Cells(lrowTable + 1, 3).Value = s 
    wsInsTL.Cells(lrowTable + 1, 4).Formula = "=Counta(" & s & "!A:A) - 1"
    wsInsTL.Cells(lrowTable + 1, 7).Formula = "=Countif(" & s & "!$H:$H,Vlookup(G$5,OTHER!$N$1:$O$10,2,false))"

End If