VBA is changing the Cell references of a Array formula causing it to malfunction?

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
107
Office Version
  1. 2019
Platform
  1. Windows
Windows 10
Excel 2019

I am using VBA to enter an array formula and for some reason it is altering the cell references and when it copies the formula down a column it does not auto increment the cell references in the same way that copying or dragging down would do when the same formula is entered manually using Ctrl Shift Enter does.

To keep this post clean and to the point I have not posted my whole VBA module as it is rather large and all other parts of it perform as expected but if it would help then please tell me and I will post it in its intierity.

This is the array formula that works perfectly if entered manually using CSE as described above.
VBA Code:
=MID(FolderDataImport!A1,FIND("-",FolderDataImport!A1)+2,MIN(FIND({"[","("},FolderDataImport!A1))-FIND("-",FolderDataImport!A1)-3)

When copied or dragged down the Cell reference is auto incrimented like:

FolderDataImport!A1
FolderDataImport!A2
FolderDataImport!A3

and so on as long as there is data in the cells in FolderDataImport!A column.

To convert it to use in VBA I used macro recorder while I entered the array formula which then turned the formula into:
VBA Code:
Selection.FormulaArray = "=MID(FolderDataImport!R[-7]C[-2],FIND(""-"",FolderDataImport!R[-7]C[-2])+2,MIN(FIND({""["",""(""},FolderDataImport!R[-7]C[-2]))-FIND(""-"",FolderDataImport!R[-7]C[-2])-3)"

After running the VBA sub the array it has actually entered iinto the cells in the sheet is
VBA Code:
{=MID(FolderDataImport!A1048571,FIND("-",FolderDataImport!A1048571)+2,MIN(FIND({"[","("},FolderDataImport!A1048571))-FIND("-",FolderDataImport!A1048571)-3)}

as you can see it has added "048571" to the end of each cell refernce and it enters this all the way down the column with out incrimenting the reference causing a #VALUE error.

Array formula's are rather over my head so if anyone can tell me what is going wrong I would be very grateful.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try...
VBA Code:
Range("C1").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

Change Range("C1") to your actual cell you are putting the formula in
 
Upvote 0
Try...
VBA Code:
Range("C1").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

Change Range("C1") to your actual cell you are putting the formula in
Hi Mark and thank you for your suggestion.

Your code works in that it enters the array formula correctly but it does not copy it down the C columm as long as there is data in the FolderDataImport!A column.

I tried the variations that my limited knowledge allowed but these produce errors.

So that anyone reading this can see how the array formula fits in the module as a whole and the different variations of your suggested formula (See Column C Formula) I include my full module code.

VBA Code:
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 = "SONG VERSION"
            [E1].Value = "PACK TYPE"
            [F1].Value = "TRACK COUNT"
            [G1].Value = "SAMPLE RATE"
            [H1].Value = "BIT RATE"
            [I1].Value = "FILE TYPE"
        
'       Column A Formula
        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),""_"","" ""))"
 

' -----------------------------------------------------------------------------------------

'I TRIED YOUR FORMULA JUST AS YOU WROTE IT
 
'       Column C Formula
        Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

'AND
        Range("C2:C").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

'I THEN TRIED

        TWs.Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

'AND

        TWs.Range("C2:C").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

' -----------------------------------------------------------------------------------------


'       Column D Formula
        TWs.Range("D2:D" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""["",FolderDataImport!A1)-2),FIND("" - "",FolderDataImport!A1)+3,LEN(FolderDataImport!A1)),""_"","" ""))"
    
'       Column E Formula
        TWs.Range("E2:E" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",SUBSTITUTE(MID(LEFT(FolderDataImport!A1,FIND(""]"",FolderDataImport!A1)-1),FIND(""["",FolderDataImport!A1)+1,LEN(FolderDataImport!A1)),""_"","" ""))"
    
'       Column F Formula
        TWs.Range("F2:F" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",LOOKUP(9^9,0+RIGHT(LEFT(FolderDataImport!A1,FIND("" Tracks"",FolderDataImport!A1)-1),ROW($1:$99)))&"" Tracks"")"
    
'       Column G Formula
        TWs.Range("G2:G" & Lr + 1).Formula = "=IF(FolderDataImport!A1="""","""",LOOKUP(9^9,0+RIGHT(LEFT(FolderDataImport!A1,FIND("" kHz"",FolderDataImport!A1)-1),ROW($1:$99)))&"" kHz"")"
    
'       Column H Formula
        TWs.Range("H2:H" & 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),"""")))))),""_"","" ""))"
    
'       Column I Formula
        TWs.Range("I2:I" & 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"")))))))"
        
        ActiveSheet.Range(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1), ActiveSheet.Cells(Rows.Count, 1)).EntireRow.RowHeight = 10
        Application.ScreenUpdating = True
End Sub
 
Upvote 0
How about
VBA Code:
TWs.Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"
TWs.Range("C2:C" & Lr + 1).FillDown
 
Upvote 0
This works

TWs.Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

TWs.Range("C2").Copy TWs.Range("C3:C" & Lr + 1)
 
Upvote 0
but it does not copy it down the C columm as long as there is data in the FolderDataImport!A column.
That is because you asked about correcting the formula error and not about doing the filldown (by what you put in post 1 you can't tell if you were doing the filldown manually or via code).

Both the codes by Fluff and kvsrinivasamurthy work for me.

and just to add a convoluted way of doing it

VBA Code:
    Dim x As String
    TWs.Range("C1").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"
    x = TWs.Cells(1, "C").Formula
    With TWs.Range(TWs.Cells(1, "C"), TWs.Cells(Lr, "C"))
        .Formula = x
        .FormulaArray = .FormulaR1C1
    End With
 
Last edited:
Upvote 0
That is because you asked about correcting the formula error and not about doing the filldown (by what you put in post 1 you can't tell if you were doing the filldown manually or via code).

Both the codes by Fluff and kvsrinivasamurthy work for me.

and just to add a convoluted way of doing it

VBA Code:
    Dim x As String
    TWs.Range("C1").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"
    x = TWs.Cells(1, "C").Formula
    With TWs.Range(TWs.Cells(1, "C"), TWs.Cells(Lr, "C"))
        .Formula = x
        .FormulaArray = .FormulaR1C1
    End With
Hi Mark

Sorry the misunderstanding but I thought that from the fact that I went into detail regarding the copying down it would be understood that was part of what I was trying to achieve.

However, thank you for your new suggestion which does appear to work, as does those suggested by Fluff and kvsrinivasamurthy.

Thanks again.
 
Upvote 0
This works

TWs.Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"

TWs.Range("C2").Copy TWs.Range("C3:C" & Lr + 1)
Thank you - it seems to work perfectly.
 
Upvote 0
How about
VBA Code:
TWs.Range("C2").FormulaArray = "=MID(FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+2,MIN(FIND({""["",""(""},FolderDataImport!A1))-FIND(""-"",FolderDataImport!A1)-3)"
TWs.Range("C2:C" & Lr + 1).FillDown
HI

Thank you - it seems to work perfectly.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top