0

I am trying to paste a formula in Excel cell via VBA but with no luck. When i try this code it perfectly works -

ActiveSheet.Range("B7").Value = "=VLOOKUP(A7,$A$1:$B$3,2,0)"

But when i try this code

ActiveSheet.Range("B7").Value = "=VLOOKUP(CONCATENATE(VLOOKUP(LOOKUP(2,1/($A$5:A7<>""),$A$5:A7),Instructions!$A$4:$B$40,2,0),NTFS!XFC7-2),'Trial Balance'!$I$55:$J$1048576,2,0)"

it ends up with an Error 1004 Run time error - Application defined or Object defined error.

I just want the formula to be pasted into the cell any other code would work. Please help me with the reason why this error occurs, and also request you to come up with a crack or an alternative to paste the formula.

Thanks in Advance!

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Ankush Surana
  • 3
  • 1
  • 5

2 Answers2

1

Try this:

ActiveSheet.Range("B7").Formula = "=VLOOKUP(CONCATENATE(VLOOKUP(LOOKUP(2,1/($A$5:A7<>""""),$A$5:A7),Instructions!$A$4:$B$40,2,0),NTFS!XFC7-2),'Trial Balance'!$I$55:$J$1048576,2,0)"

Note that value was replaced with formula and the quotes after <> were doubled.

z32a7ul
  • 3,695
  • 3
  • 21
  • 45
0

Try This:

ActiveSheet.Range("B7").FormulaR1C1 = "=VLOOKUP(CONCATENATE(VLOOKUP(LOOKUP(2,1/($A$5:A7<>""),$A$5:A7),Instructions!$A$4:$B$40,2,0),NTFS!XFC7-2),'Trial Balance'!$I$55:$J$1048576,2,0)"
Atik
  • 57
  • 7
  • 1
    You need quadruple quotes in the middle `A7<>""""` – ashleedawg Dec 03 '17 at 09:38
  • FormulaR1C1 is used with a rare type of cell referencing, like this: Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10" – z32a7ul Dec 03 '17 at 09:52
  • Thanks Man! This thing works! – Ankush Surana Dec 03 '17 at 14:56
  • @AnkushSurana Actually, this answer suffers from the same problem as your original code. (The string becomes `=VLOOKUP(CONCATENATE(VLOOKUP(LOOKUP(2,1/($A$5:A7<>"),$A$5:A7),Instructions!$A$4:$B$40,2,0),NTFS!XFC7-2),'Trial Balance'!$I$55:$J$1048576,2,0)`, which is not a valid formula.) I'm surprised you marked it as the accepted answer. – YowE3K Dec 03 '17 at 21:57