带可变工作表名的VLOOKUP返回0值问题求助

问题根源 #

• 变量名错误：代码里定义了SheetName存储目标工作表名，但公式中误用了未定义的StringVal变量，导致生成的公式里工作表名缺失（从公式栏快照能看到''!R4C12:...的空工作表名），VLOOKUP无法定位到正确的查找区域，最终触发IFERROR返回0。
• 潜在的列索引或数据类型问题：
  • VLOOKUP第3参数是查找区域内的列索引，当前公式用的是2，对应查找区域R4C12:R...C14（L列到N列）的第2列（M列），需确认是否是你要返回的目标列；若目标值在N列，索引应改为3。
  • 若查找值（J列，RC[-2]）和查找区域首列（L列）的数据类型不匹配（比如一个是文本、一个是数字），也会导致匹配失败返回0。

修正后的代码 #

Dim SheetName As String: SheetName = DestinationWs.Name

Dim OldRemainingHoursLastRowFirst As Long
OldRemainingHoursLastRowFirst = DestinationWs.Range("W3").End(xlDown).Row

' 替换正确的变量名，修复公式中的工作表名引用
DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],'" & SheetName & "'!R4C12:R" & OldRemainingHoursLastRowFirst & "C14,2,0),0)"

额外优化建议 #

• 规范变量类型：行号用Long类型更合适，工作表名用String类型，避免不必要的Variant类型。
• 安全获取最后一行：End(xlDown)如果遇到空行会跳错，建议改用DestinationWs.Cells(DestinationWs.Rows.Count, "W").End(xlUp).Row来获取W列真正的最后一行数据行。
• 统一数据类型：如果存在数据类型不匹配的情况，可通过函数转换，比如把查找值转为文本：VLOOKUP(TEXT(RC[-2],"@"),'" & SheetName & "'!R4C12:R" & OldRemainingHoursLastRowFirst & "C14,2,0)，或者转为数字：VLOOKUP(VALUE(RC[-2]),...,2,0)。

内容的提问来源于stack exchange，提问作者Mostafa Nabil