1

I am trying to write the following formula in VBA however, since the formula contains an apostrophe, the part after the apostrophe gets commented out. Can anyone help please?

Dim LastcolGen As Long, lastrowfilter As Long 

Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Formula = _
 "IF(J$4="FactTrancheValue",INDEX(INDIRECT(J$4),ROW()-4,MATCH(J$5,INDIRECT("'"&J$4&"'!"&"A1:JJ1"),0)),INDEX(INDIRECT(J$4),MATCH(INDIRECT(J$3&ROW()),INDIRECT("'"&J$4&"'!"&"A1:A999999"),0),MATCH(J$5,INDIRECT("'"&J$4&"'!"&"A1:JJ1"),0)))" 
Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues

Thanks, AB

user12206869
  • 13
  • 3

1 Answers1

0

Try this (untested)

Range(Cells(6, 10), Cells(lastrowfilter, LastcolGen)).Formula = _
    "=IF(J$4=""FactTrancheValue"",INDEX(INDIRECT(J$4)," & _
    "ROW()-4,MATCH(J$5,INDIRECT(""'""&J$4&""'!""&""A1:JJ1""),0))," & _
    "INDEX(INDIRECT(J$4),MATCH(INDIRECT(J$3&ROW())," & _
    "INDIRECT(""'""&J$4&""'!""&""A1:A999999""),0)," & _
    "MATCH(J$5,INDIRECT(""'""&J$4&""'!""&""A1:JJ1""),0)))"

(edited according to your update)

When setting that formula to a single cell I get no errors from VBA (though the formula itself returns #REF!)

If this doesn't work then make sure your Range() reference is correct.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Results in the following error: "Application-defined or object-defined error". – user12206869 Oct 13 '19 at 07:04
  • I can't test right now - Debug.Print the formula to the Immediate pane then copy and paste into the worksheet - Excel will tell you where the problem is. – Tim Williams Oct 13 '19 at 07:06
  • Okay, I tried however, not very sure about how quotes are used in excel. Whenever you may find some time, please help debug. Thanks a lot! – user12206869 Oct 13 '19 at 07:14
  • What value is in J4 ? Looks like it should be a sheet name, but then you have `INDEX(INDIRECT(J$4),...` which doesn't seem valid – Tim Williams Oct 13 '19 at 19:40
  • J4 has a value equal to a named range in the workbook. – user12206869 Oct 14 '19 at 03:00
  • Seems more like a problem with your formula (which is not my strong point). If you can get that working in a cell and update your question with the working formula, I can help you translate that. – Tim Williams Oct 14 '19 at 19:39
  • Have updated my question. This formula works when entered in a cell. – user12206869 Oct 15 '19 at 07:46
  • I can't replicate your setup, but I've edited my code above. If it still doesn't work then I'm out of suggestions. – Tim Williams Oct 15 '19 at 17:37