转换Excel VBA范围以在Excel函数中使用-Java 学习之路

我想将VLOOKUP公式放入VBA宏中的Excel单元格中 . 用作VLOOKUP第二个参数的范围在宏中标识为Range对象 . 范围是两列宽，但行数可变 . 这是我的宏代码，显然会抛出一个Excel错误：

Dim processedRow As Integer
Dim rngVlookupData As Range
Dim ws As Worksheet
... code ...
Set ws = ThisWorkbook.Sheets("HR Eval Report")
ws.Range("BC8", "BBD8").Select
ws.Range(Selection, Selection.End(xlDown)).Select
Set rngVlookupData = Selection
Range(Cells(processedRow, 26), Cells(processedRow, 26)) = "=IF(VLOOKUP(Z7,rngVlookupData,1)=Z7,""Yes"","""")"
Range("AA5") = "=IF(VLOOKUP(Z7,rngVlookupData,1)=Z7,VLOOKUP(Z7,rngVlookupData,2),"""")"

显然，Excel无法识别“rngVlookupData”，这会导致#NAME错误 . 任何帮助深表感谢 .

1 回答

那是因为它被视为一个字符串 . 你这个

Range("AA5").Formula = "=IF(VLOOKUP(Z7," & _
                       rngVlookupData.Address & _
                       ",1)=Z7,VLOOKUP(Z7," & _
                       rngVlookupData.Address & _
                       ",2),"""")"`

回复于 2024-04-20T02:52:07+08:00

转换Excel VBA范围以在Excel函数中使用

1 回答

相关问题