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

E

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
122
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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
MARK858 said:
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
Click to expand...
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
Event2020 said:
but it does not copy it down the C columm as long as there is data in the FolderDataImport!A column.
Click to expand...
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
MARK858 said:
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
Click to expand...
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
kvsrinivasamurthy said:
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)
Click to expand...
Thank you - it seems to work perfectly.
 
Upvote 0
Fluff said:
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
Click to expand...
HI

Thank you - it seems to work perfectly.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Formula to make combination of elements given possible variables?
Replies
5
Views
249
felixstraube
F
C
  • Solved
Entering formula in A1 mode works but in R1C1 mode fails
Replies
5
Views
319
Alex Blakenburg
A
J
  • Question Question
Changing multiple cell references to absolute references within formula
Replies
1
Views
459
JWill78
J
F
  • Question Question
Excel references changing
2
Replies
10
Views
381
Joe4
Joe4
G
  • Question Question
Weird drag-and-drop behavior
Replies
1
Views
298
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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