当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - FormulaArray with variable concatenated into it

问题描述:

I'm trying to use VBA to insert a FormulaArray. It works when I hardcode the cell rows but when I try to use a variable (shipRow) then I get the error "Unable to set the FormulaArray proprerty of the Range class". In this example, shipRow = 4 and voyageColumn = 6

THIS WORKS:

Cells(shipRow + 6, voyageColumn).FormulaArray = "=IFERROR(VLOOKUP(CONCATENATE(F$4,F$5,$B$1,G$5,F$7,D10,""Lower""),'Price Recs'!$2:$1048576,20,FALSE),"""")"

THIS DOES NOT WORK:

Cells(shipRow + 6, voyageColumn).FormulaArray = "=IFERROR(VLOOKUP(CONCATENATE(F$" & Str(shipRow) & ",F$" & Str(shipRow + 1) & ",$B$1,G$5,F$7,D" & Str(shipRow + 6) & ",""Lower""),'Price Recs'!$2:$1048576,20,FALSE),"""")"

This is probably an easy fix that I just can't find!

分享给朋友:
您可能感兴趣的文章:
随机阅读: