0

I have a formula that is inserted into my spread sheet using VBA when new data is imported (also by VBA) and it worked perfectly.

However I have had to expand the formula and now it is too large to fit on a single line within Excels VBA editor, plus as it is becoming some what complicated, I would like to split it over several lines.

I have tried adding ( _) space underscore at the end of each break but now I get a Compile Error.

This is the formula BEFORE converting it to 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),""))))),"_"," "))

This is the formula AFTER converting it to VBA (which works fine until I try to spread it over several lines)

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),""""))))),""_"","" ""))"

This is how I would like to break it up

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),""""))))),""_"","" ""))"
user2303407
  • 59
  • 9

1 Answers1

1

This code will work without error.

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),""""))))),""_"","" ""))"
norie
  • 9,609
  • 2
  • 11
  • 18