1

I am having an issue for excel formula index/match with IFERROR and it is working fine in excel when I am running it manually however after writing into Macro it is not working. this formula is created for doing matching and look-ups at multiple conditions and give the output result.

I am getting following error while running macro Run-time error'1004': Unable to set the FormulaArray property of the Range class.

   Range("O2").Select

   Selection.FormulaArray = _
 "=IFERROR(INDEX('[NAL for Macro.xlsb]Sheet1'!C13,MATCH(1,('[NAL for Macro.xlsb]Sheet1'!C8=RC[-8])*(LEFT('[NAL for Macro.xlsb]Sheet1'!C3,15)=LEFT(RC[-13],15)),0)),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)&""*"",'[NAL for Macro.xlsb]Sheet1'!C3:C15,11,0)))))"

then I splitted the formula in to two parts as done below. Macro coding is running and not giving any error but replace doesn't happen. I am bit new to macro and tried hard as per my knowledge but it is not working Can anyone please help to guide me.

 Sub Macro19()
 '
 ' Macro19 Macro
 '

 '    
  With ActiveSheet.Range("O2")
 Selection.FormulaArray = "=IFERROR(INDEX('[NAL for Macro.xlsb]Sheet1'!C13,MATCH(1,('[NAL for Macro.xlsb]Sheet1'!C8=RC[-8])*(LEFT('[NAL for Macro.xlsb]Sheet1'!C3,15)=LEFT(RC[-13],15)),0)),XX)"

  .Replace "XX", "IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'[NAL for Macro.xlsb]Sheet1'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)&""*"",'[NAL for Macro.xlsb]Sheet1'!C3:C15,11,0)))))"

      End With
      End Sub

So far I have changed coding with .Replace "'ws'", "'[NAL for Macro.xlsb]Sheet1'" and below is the revised. It is running successfully and no error is coming back but .Replace doesn't happening till yet and output result is coming #NA. Please help to resolve this.

 Sub Macro20()
 '
 ' Macro20 Macro
 '

 '

   With ActiveSheet.Range("O2")
         .FormulaArray = _
        "=IFERROR(INDEX('ws'!C13,MATCH(1,('ws'!C8=RC[-8])*(LEFT('ws'!C3,15)=LEFT(RC[-13],15)),0)),IFERROR(VLOOKUP(RC[-8],'ws'!C8:C15,6,0),IFERROR(VLOOKUP(RC[-8],'ws'!C9:C15,5,0),(VLOOKUP(LEFT(RC[-13],15)&""*"",'ws'!C3:C15,11,0)))))"

       .Replace "'ws'", "'[NAL for Macro.xlsb]Sheet1'"
     End With

    End Sub
manjeet singh
  • 65
  • 6
  • There's good advice on this subject here: https://www.mrexcel.com/board/threads/long-array-formulas-in-vba.687125/ . Maybe there's some syntax error on the partial formulas. Have you tried using a numeric value instead of XX? – Ivan Jan 31 '21 at 05:42
  • I have run the formula in partials and it is running successfully and when i am trying to run complete formula at one time; getting error. Yes I have also tried numeric values as well but getting the same error. – manjeet singh Jan 31 '21 at 06:05
  • I would look for multiple occurrances I can replace, then work backwards from the formula replacing and noting the order (but not manually editing the formula to try and avoid error) to replace when building the sentence. My first replacement would be `.Replace "'ws'", "'[NAL for Macro.xlsb]Sheet1'"` (the apostrophes make nice string matches) which cuts the formula down a lot but has minimal impact. Replace further sub functions until within the character limits – Tragamor Jan 31 '21 at 12:19
  • 1
    Actually, that single replacement should work as according to https://charactercounttool.com/ the .FormulaArray text would then be 222 characters – Tragamor Jan 31 '21 at 12:28
  • I have done the same but .Replace is not working means it's not replacing "'ws'" to "'[NAL for Macro.xlsb]Sheet1'". Code run without error and output result is coming as #NA. – manjeet singh Jan 31 '21 at 13:57
  • .Replace() is applied on content of ActiveSheet.Range("O2"), not on .FormulaArray. – jacouh Jan 31 '21 at 16:25
  • @jacouh can you please help with correct formula so that .Replace() could direct to formula only. Bit new to coding world and i have already tried lot of options at my level. – manjeet singh Jan 31 '21 at 16:59
  • Replace formuaArray itself very easy, but is useless, as the resulting formula will have a length of 365 characters that will be > 255, so error. You could shorten filepath using m.xlsb instead of [NAL for Macro.xlsb], by copy the file in the same directory then the current Workbook. Or elaborating a VBA function replacing Excel very long 1-phrase string. – jacouh Jan 31 '21 at 17:49
  • You may find that it is erroring as you seem to be mixing R1C1 and A1 notation in the formula. https://stackoverflow.com/questions/49325003/vba-r1c1-notation-not-being-recognised-with-range-formulaarray You may find you need to break down the formulae into smaller sections and check they are returning what you expect by either using Debug.Print or outputting results to a cell. – Tragamor Jan 31 '21 at 21:18

0 Answers0