将长Excel VBA公式拆分为较小的多行-换行时下划线会导致编译错误

Question

我有一个公式，当导入新数据(也通过VBA(时，它会使用VBA插入到电子表格中而且效果很好。

然而，我不得不扩展这个公式，现在它太大了，无法在Excels VBA编辑器中放在一行上，而且由于它越来越复杂，我想把它分成几行。

我试过在每次中断的末尾添加(_(空格下划线，但现在我遇到了编译错误。

这是将其转换为VBA 之前的公式

=IF(FolderDataImport!A1="","",SUBSTITUTE(
IF(ISNUMBER(SEARCH("wav",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH("kHz",FolderDataImport!A1)+4,SEARCH("Wav",FolderDataImport!A1)-SEARCH("kHz",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH("flac",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH("kHz",FolderDataImport!A1)+4,SEARCH("flac",FolderDataImport!A1)-SEARCH("kHz",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH("Aif",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH("kHz",FolderDataImport!A1)+4,SEARCH("Aif",FolderDataImport!A1)-SEARCH("kHz",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH("Mp3",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH("Kbps",FolderDataImport!A1)-4,SEARCH("Mp3",FolderDataImport!A1)-SEARCH("mp3",FolderDataImport!A1)+9),
IF(ISNUMBER(SEARCH("Mogg",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH("kHz",FolderDataImport!A1)+4,SEARCH("Mogg",FolderDataImport!A1)-SEARCH("kHz",FolderDataImport!A1)-5),""))))),"_"," "))

这是将其转换为VBA后的公式(在我尝试将其扩展到几行之前，它运行良好(

TWs.Range("G2:G" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(IF(ISNUMBER(SEARCH(""wav"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""Wav"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),IF(ISNUMBER(SEARCH(""flac"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""flac"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),IF(ISNUMBER(SEARCH(""aif"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""aif"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),IF(ISNUMBER(SEARCH(""mp3"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kbps"",FolderDataImport!A1)+5,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""mp3"",FolderDataImport!A1)+7),IF(ISNUMBER(SEARCH(""mogg"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""mogg"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),""""))))),""_"","" ""))"

这就是我想打破的方式

TWs.Range("G2:G" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(
IF(ISNUMBER(SEARCH(""wav"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""Wav"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH(""flac"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""flac"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH(""aif"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""aif"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),
IF(ISNUMBER(SEARCH(""mp3"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kbps"",FolderDataImport!A1)+5,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""mp3"",FolderDataImport!A1)+7),
IF(ISNUMBER(SEARCH(""mogg"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""mogg"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),""""))))),""_"","" ""))"

Answer 1

此代码将正常工作。

TWs.Range("G2:G" & lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(" & _
"IF(ISNUMBER(SEARCH(""wav"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""Wav"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5)," & _
"IF(ISNUMBER(SEARCH(""flac"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""flac"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5)," & _
"IF(ISNUMBER(SEARCH(""Aif"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""Aif"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5)," & _
"IF(ISNUMBER(SEARCH(""Mp3"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""Kbps"",FolderDataImport!A1)-4,SEARCH(""Mp3"",FolderDataImport!A1)-SEARCH(""mp3"",FolderDataImport!A1)+9)," & _
"IF(ISNUMBER(SEARCH(""Mogg"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""Mogg"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),""""))))),""_"","" ""))"