Hi all,
I'm trying to add a formula into a cell by VBA
Sub test()
Sheets(4).Select
ActiveWorkbook.Names.Add "tests", RefersTo:="=MATCH(INDIRECT(""B""&ROW()),$A:$A,-1)"
Cells(1, 3).Formula = "=tests"
End Sub
However, it returns the #VALUE error because ROW() returns the value in the form of array (I think) like {1}, then it caused the error to the Indirect function which cannot process input like Indirect("B" & {1}).
However, if I paste this formula: =MATCH(INDIRECT(""B""&ROW()),$A:$A,-1)
directly into the cells, it works nicely.
It also works in VBA if I use only Indirect function
Anyone has ideas about this problem?
Thanks
Bookmarks