0

I have the following named ranges (some of which are INDEX/MATCH dynamic ranges) and their accompanying formulas that are present in my workbook's Name Manager:

  • Q1_BusinessWorkd_ID ='Q1 Data'!$C$35:INDEX('Q1 Data'!$G$35:$G$1048576,MATCH("zzzzz",'Q1 Data'!$C$35:$C$1048576,1)-1)
  • Q1_Data ='Q1 Data'!$B$35:INDEX('Q1 Data'!$CF$35:$CF$1048576,MATCH("zzzzz",'Q1 Data'!$C$35:$C$1048576,1))
  • Q1_Data_Start ='Q1 Data'!$B$35
  • Q1_Row1_Column_Formulas ='Q1 Data'!$H$35:$CF$35
  • Q1_Vlookup_Data ='Q1 Data'!$C$35:INDEX('Q1 Data'!$CF$35:$CF$1048576,MATCH("zzzzz",'Q1 Data'!$C$35:$C$1048576,1))

The formulas that DON'T contain the INDEX and MATCH statements can be added to the workbook's Named Ranges using the folowing VBA code:

ActiveWorkbook.Names.Add Name:="Q1_Data_Start", RefersTo:="='Q1 Data'!$B$35"
ActiveWorkbook.Names.Add Name:="Q1_Row1_Column_Formulas", RefersTo:="='Q1 Data'!$H$35:$CF$35"

However, the formulas that contain the INDEX and MATCH statements generate a compile syntax error. I have no idea how to correct the syntax of the INDEX - MATCH formulas to not generate the syntax error.

Anyone know how to correct this problem?

Ken White
  • 123,280
  • 14
  • 225
  • 444
Bill Vallance
  • 471
  • 3
  • 19
  • 1
    You need to escape any double quotes in a string by doubling them up so (eg) `MATCH("zzzzz",` should be `MATCH(""zzzzz"",` – Tim Williams Mar 01 '21 at 00:56
  • Thanks, Tim! I forgot all about that. That's exactly what the debugger reported as the problem I'll try the double quotes and report back. – Bill Vallance Mar 01 '21 at 01:31
  • Tim - Your solution worked perfectly. If you want to answer the question with your comment I'll award the answer to you. Thanks for your help. – Bill Vallance Mar 01 '21 at 16:36

1 Answers1

1

You need to escape any double quotes in a string by doubling them up so (eg)

...MATCH("zzzzz", ...

should be

...MATCH(""zzzzz"", ...
Tim Williams
  • 154,628
  • 8
  • 97
  • 125