Sub InsertFormulasPack()
Dim SWs As Worksheet, TWs As Worksheet
Dim Lr As Long
Dim Answer
Set SWs = Worksheets("FolderDataImport")
Set TWs = Worksheets("Pack")
Lr = SWs.Range("A" & SWs.Rows.Count).End(xlUp).Row
Answer = MsgBox("Would You Like To Insert Pack Formulas?", vbYesNo, "Insert Pack Formulas")
If Answer <> vbYes Then Exit Sub
Application.ScreenUpdating = False
Sheets("Pack").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
ActiveSheet.Columns.ColumnWidth = 1
Cells.EntireColumn.AutoFit
For I = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(I).ColumnWidth = Columns(I).ColumnWidth + 6
Columns(I).Rows.RowHeight = 18
Columns("A:A").Select
Selection.ColumnWidth = 8
Columns("B:B").Select
Selection.ColumnWidth = 38
Columns("C:C").Select
Selection.ColumnWidth = 45
Columns("D:D").Select
Selection.ColumnWidth = 22
Columns("E:E").Select
Selection.ColumnWidth = 16
Columns("F:F").Select
Selection.ColumnWidth = 16
Columns("G:G").Select
Selection.ColumnWidth = 16
Columns("H:H").Select
Selection.ColumnWidth = 16
Next I
Range("A:H").CurrentRegion.ClearContents
Range("A1:H1").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("A1:H1").Borders(xlEdgeBottom).Weight = xlMedium
Range("A1").EntireRow.Font.Bold = True
Range("A1").EntireRow.VerticalAlignment = xlCenter
[A1].Value = "LOOKUP"
[B1].Value = "ARTIST"
[C1].Value = "SONG TITLE"
[D1].Value = "PACK TYPE"
[E1].Value = "TRACK COUNT"
[F1].Value = "SAMPLE RATE"
[G1].Value = "BIT RATE"
[H1].Value = "FILE TYPE"
' Column A Formula
TWs.Range("A2:A" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",TEXT(ROW(FolderDataImport!A1),""000""))"
' TWs.Range("A2:A" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",TEXT(ROW(FolderDataImport!A1),""000""),"""")"
' Column B Formula
TWs.Range("B2:B" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(LEFT(FolderDataImport!A1,FIND(""_-_"",FolderDataImport!A1)-1),""_"","" ""))"
' TWs.Range("B2:B" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",SUBSTITUTE(LEFT(FolderDataImport!A1,FIND(""_-_"",FolderDataImport!A1)-1),""_"","" ""))"
' Column C Formula
TWs.Range("C2:C" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""["",FolderDataImport!A1)-2),FIND(""_-_"",FolderDataImport!A1)+3,LEN(FolderDataImport!A1)),""_"","" ""))"
' TWs.Range("C2:C" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""["",FolderDataImport!A1)-2),FIND(""_-_"",FolderDataImport!A1)+3,LEN(FolderDataImport!A1)),""_"","" ""))"
' Column D Formula
TWs.Range("D2:D" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""]"",FolderDataImport!A1)-1),FIND(""["",FolderDataImport!A1)+1,LEN(FolderDataImport!A1)),""_"","" ""))"
' TWs.Range("D2:D" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""]"",FolderDataImport!A1)-1),FIND(""["",FolderDataImport!A1)+1,LEN(FolderDataImport!A1)),""_"","" ""))"
' Column E Formula
TWs.Range("E2:E" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",LOOKUP(9^9,0+RIGHT(LEFT(FolderDataImport!A1,FIND(""_Tracks"",FolderDataImport!A1)-1),ROW($1:$99)))&"" Tracks"")"
' TWs.Range("E2:E" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",SUBSTITUTE(IFERROR(MID(LEFT(FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""]"",FolderDataImport!A1))-1),FIND(""("",FolderDataImport!A1,FIND(""]"",FolderDataImport!A1))+1,99),""""),""_"","" ""),"""")"
' Column F Formula
TWs.Range("F2:F" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",LOOKUP(9^9,0+RIGHT(LEFT(FolderDataImport!A1,FIND(""_kHz"",FolderDataImport!A1)-1),ROW($1:$99)))&"" kHz"")"
' TWs.Range("F2:F" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""]"",FolderDataImport!A1))-1),FIND(""("",FolderDataImport!A1,FIND(""]"",FolderDataImport!A1))+1,99),""_"","" ""))"
' TWs.Range("F2:F" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",LOOKUP(9^9,0+RIGHT(LEFT(FolderDataImport!A1,FIND(""_kHz"",FolderDataImport!A1)-1),ROW($1:$99)))&"" kHz"","""")"
' Column G Formula
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)-4)," & _
"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(""macOS"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""macOS"",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),"""")))))),""_"","" ""))"
' 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(""mp3"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kbps"",FolderDataImport!A1)+5,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""mp3"",FolderDataImport!A1)+7)&"" (""&MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-12)&"")"",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(""mp3"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kbps"",FolderDataImport!A1)+5,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""mp3"",FolderDataImport!A1)+7)&"" (""&MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""mp3"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-12)&"")"",IF(ISNUMBER(SEARCH(""mogg"",FolderDataImport!A1)),MID(FolderDataImport!A1,SEARCH(""kHz"",FolderDataImport!A1)+4,SEARCH(""mogg"",FolderDataImport!A1)-SEARCH(""kHz"",FolderDataImport!A1)-5),"""")))),""_"","" ""),"""")"
' Column H Formula
TWs.Range("H2:H" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""", IF(ISNUMBER(SEARCH(""wav"",FolderDataImport!A1)),""Wav"", IF(ISNUMBER(SEARCH(""flac"",FolderDataImport!A1)),""Flac"", IF(ISNUMBER(SEARCH(""macOS"",FolderDataImport!A1)),""macOS"", IF(ISNUMBER(SEARCH(""aif"",FolderDataImport!A1)),""Aif"", IF(ISNUMBER(SEARCH(""mp3"",FolderDataImport!A1)),""MP3"", IF(ISNUMBER(SEARCH(""mogg"",FolderDataImport!A1)),""Mogg"",""no"")))))))"
' TWs.Range("H2:H" & Lr + 1).Formula = "=IF(FolderDataImport!A1>"""",IF(ISNUMBER(SEARCH(""wav"",FolderDataImport!A1)),""Wav"", IF(ISNUMBER(SEARCH(""flac"",FolderDataImport!A1)),""Flac"",IF(ISNUMBER(SEARCH(""mp3"",FolderDataImport!A1)),""MP3"", IF(ISNUMBER(SEARCH(""mogg"",FolderDataImport!A1)),""Mogg"",""no"")))),"""")"
Worksheets("Pack").Columns("A:H").SpecialCells(xlconstrants).Interior.Color = vbYellow
ActiveSheet.Range(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1), ActiveSheet.Cells(Rows.Count, 1)).EntireRow.RowHeight = 10
Application.ScreenUpdating = True
End Sub