在VBA公式中使用范围变量。 [英] Using a range variable inside a VBA formula.
问题描述
根据以下代码,我收到#Name错误作为此公式的值。在进行公式评估时,它直接来自"范围"。并且首先是"细胞"。公式的一部分。基本上,我有一个变量"DNCN"。
根据依赖标准而变化,我需要在公式中考虑这种变化的可能性。
Based on the below code, I'm receiving a #Name error as a value for this formula. When doing a formula evaluation, it's coming directly from the "Range" and first "Cell" portions of the formula. Essentially, I have a variable "DNCN" that changes based upon dependent criteria, and I need to allow for this possibility of change within the formula.
范围(" ; BM6",Cells(LRC2,LDCol))。公式=" = INDEX('Sku Detail'!$ 15:$ 10000,MATCH($ J6,'Sku Detail'!$ H:$ H,0),MATCH(BM $ 5,范围(单元格(15,"& DNCN&"),单元格(15,400)),0))"
Range("BM6", Cells(LRC2, LDCol)).Formula = "=INDEX('Sku Detail'!$15:$10000,MATCH($J6,'Sku Detail'!$H:$H,0),MATCH(BM$5, Range(Cells(15," & DNCN & "), Cells(15, 400)),0))"
" Range(Cells) - 当评估公式时,它们都显示为#Name。您是否碰巧知道将我的变量合并到公式结构中的解决方案?
"Range(Cells" - both these are showing as #Name when the formula is evaluated. Would you happen to know a solution to incorporating my variable into my formula structure?
提前致谢!
推荐答案
您无法使用VBA关键字Range试用这个版本:
You can't use the VBA keyword Range within a worksheet formula. Try this version:
Range("BM6", Cells(LRC2, LDCol)).FormulaR1C1 = _
"=INDEX('Sku Detail'!R15:R10000,MATCH(RC10," & _
"'Sku Detail'!C8:C8,0),MATCH(R5C, R15" & DNCN & ":R15C400,0))"
这篇关于在VBA公式中使用范围变量。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!