在IF公式中引用Workbook变量 [英] Referencing a Workbook variable in IF formula

问题描述

我有一个宏，用于比较两个工作簿之间的数字，并输出是否正确或不正确。
其中一个工作簿被存储为一个变量，因为它引用了上个月的名字。

I have a macro which is comparing numbers between two workbooks and outputting if it's correct or incorrect. One of the workbooks is stored as a variable as it references last month in it's name.

Dim wb As Workbook
Set wb = Workbooks("Monthly Life Management Report " & Format(DateAdd("m", -1, Date), "mmmm yyyy") & ".xlsm")

运行宏的最后一部分时，我得到一个应用程序或对象定义的错误。

I am getting a application or object defined error when running the final part of the macro.

 Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=IF([wb]'2 Claims'!R8C5 =[Template.xlsx]Claims!R8C5,""Correct"",""Incorrect"")"

整个脚本

Sub Monthly_Life_Management()


Dim thisWb As Workbook
Set thisWb = ActiveWorkbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\Validation_File_" & Format(Date, "dd mm yy") & ".xls"


Dim wb As Workbook
Set wb = Workbooks("Monthly Life Management Report " & Format(DateAdd("m", -1, Date), "mmmm yyyy") & ".xlsm")



'Claims Tab
 wb.Activate
 Sheets("2 Claims").Select

 Range("C2:C13").Select
 Application.CutCopyMode = False
 Selection.Copy
 Windows("Validation_File_" & Format(Date, "dd mm yy") & ".xls").Activate
 Range("C2").Select
 ActiveSheet.Paste

 wb.Activate
 Sheets("2 Claims").Select
 Range("D2:D13").Select
 Application.CutCopyMode = False
 Selection.Copy
 Windows("Validation_File_" & Format(Date, "dd mm yy") & ".xls").Activate
 Range("D2").Select
 ActiveSheet.Paste

 wb.Activate
 Sheets("2 Claims").Select
 Range("E7:G7").Select
 Application.CutCopyMode = False
 Selection.Copy
 Windows("Validation_File_" & Format(Date, "dd mm yy") & ".xls").Activate
 Range("E7").Select
 ActiveSheet.Paste

'Counts
 Range("E8").Select
 ActiveCell.FormulaR1C1 = _
"=IF('[wb]2 Claims'!R8C5 ='[Template.xlsx]Claims'!R8C5,""Correct"",""Incorrect"")"

 Cells.Select
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

推荐答案

你需要调整 / code>在工作簿参考中：

You need to tweak where the ' are in the workbook reference:

"=IF('[wb]2 Claims'!R8C5 =[Template.xlsx]Claims!R8C5,""Correct"",""Incorrect"")"

编辑：根据您的add'l代码，您将 wb 作为变量。尝试这样做

Per your add'l code, you have wb as a variable. Try this instead

"=IF('[" & wb.Name & "]2 Claims'!R8C5 =[Template.xlsx]Claims!R8C5,""Correct"",""Incorrect"")"

（注意：您可能需要或可能不需要 .Name ）

(Note: you may or may not need the .Name)

这篇关于在IF公式中引用Workbook变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！