0

I've researched as many of the "Unable to set the FormulaArray property of the range class" issues here and in other sites to troubleshoot a FormulaArray operation I'm trying to carry out in a table of data.

I've respected the 255 character limit in my formula taking into account R1C1, I've tried to insert the formula as a text string first. I've tried quite a few things.

Now I'm thinking my issue is with the fact that I'm trying to insert my formula only into blank cells because if I simplify my formula to only be =1+1 I still get the error. If I change .FormulaArray to .Formula to simply enter a standard formula I also get the same error. Is this operation not possible with blank cells?

The error occurs at the .FormulaArray = myFormula1 step.

To confirm, the formula by itself works (pasted further below) when entered into cells manually.

    Sub ArrayMacro()

    Dim myFormula1 As String 
    Dim myFormula2 As String 
    Dim myFormula3 As String 
    Dim myFormula4 As String 

    myFormula1 = "=IF(COUNTIFS(MLBtransactions!$D:$D,A$1,MLBtransactions!$A:$A,""<=""&$C2)>0,IF(INDEX(MLB," & "X_X_X)" 
    myFormula2 = "transactions!$A:$E,MATCH(1,(MLBtransactions!$D:$D=A$1)*(MLBtransactions!$A:$A<=$C2),0),," & "Y_Y_Y)" 
    myFormula3 = "5)=$A2,""DNP/SUS/MIN"",""with "" & INDEX(MLBtransactions!$A:$E,MATCH(A$1,MLBtransactions," & "Z_Z_Z)" 
    myFormula4 = "!$D:$D,0),5)),IF(COUNTIFS(MLBstats!$B:$B,A$1,MLBstats!$A:$A,$A2)=1,""DNP/SUS/MIN"",""LEAVE BLANK""))" 

    Sheets("Sheet1").Select  
    With Range("E2:AZ140").SpecialCells(4)
    .FormulaArray = myFormula1 
    .Replace ",X_X_X)", myFormula2 
    .Replace ",Y_Y_Y)", myFormula3 
    .Replace ",Z_Z_Z)", myFormula4 
    End With 

    End Sub

Question update. Using With Range("E2:AZ140").SpecialCells(xlCellTypeBlanks) instead of With Range("E2:AZ140").SpecialCells(4) also ends with the same error.

Here is the full formula being used

=IF(COUNTIFS(MLBtransactions!$D:$D,A$1,MLBtransactions!$A:$A,"<="&$C2)>0,IF(INDEX(MLBtransactions!$A:$E,MATCH(1,(MLBtransactions!$D:$D=A$1)*(MLBtransactions!$A:$A<=$C2),0),5)=$A2,"DNP/SUS/MIN","with " & INDEX(MLBtransactions!$A:$E,MATCH(A$1,MLBtransactions!$D:$D,0),5)),IF(COUNTIFS(MLBstats!$B:$B,A$1,MLBstats!$A:$A,$A2)=1,"DNP/SUS/MIN","LEAVE BLANK"))

Testing with a simplified code (example below) led to the realization as YowE3K points out that myFormula1 needs to be valid in order for the procedure to work. 

Sub ArrayMacro()

Dim myFormula1 As String
Dim myFormula2 As String

myFormula1 = "=1+1" & "+2+2"
myFormula2 = "+1+1"

Sheets("Sheet1").Select
With Range("h14:h16").SpecialCells(xlCellTypeBlanks)
MsgBox .Address
.FormulaArray = myFormula1
.Replace "+2+2)", myFormula2
End With

End Sub
Improve this question
9
  • Using .FormulaArray = myFormula1 implies that myFormula1 is a valid formula by itself. It isn't. Commented Sep 6, 2017 at 23:01
  • @YowE3K I wasn't aware of that. In myFormula1 I've now moved the IF(INDEX..... near the end to the 2nd formula and closed that IF statement in formula1 with a simple ""true"",""false"") and then use that in the replace as well. It still results in an error. As I mentioned though, even using =1+1 as a formula in myFormula1 results in the error once i reach the step .FormulaArray = myFormula1 Commented Sep 6, 2017 at 23:11
  • If you don't have any blank cells in E2:AZ140 (or even if it is a blank sheet, which means your UsedRange doesn't extend there, and therefore the cells aren't classed as being blank) your With statement will crash, but once I get past that line I have no trouble setting the FormulaArray to =1+1. Commented Sep 6, 2017 at 23:31
  • What is the actual error message you get? Commented Sep 6, 2017 at 23:33
  • The other issue you are going to have is that, if there are some non-blank cells in your destination range, you will probably have non-contiguous areas in the Range("E2:AZ140").SpecialCells(xlCellTypeBlanks) and so your formula will only be applied to the first of those areas. Commented Sep 6, 2017 at 23:47

2 Answers 2

Reset to default
1

When you set a formula using FormulaArray = ..., it needs to be a valid formula. (I think after each Replace the formula needs to continue being valid too, but I haven't tested that. Edit: No, if the Replace would create an invalid formula, it just doesn't process it - but it doesn't crash.)

Your problems all seem to stem from the use of invalid formulas in your myFormula1 variable.

I suggest you use the following:

Sub ArrayMacro()

    Dim myFormula1 As String
    Dim myFormula2 As String
    Dim myFormula3 As String
    Dim myFormula4 As String

    myFormula1 = "=IF(COUNTIFS(MLBtransactions!$D:$D,A$1,MLBtransactions!$A:$A,""<=""&$C2)>0,IF(1232=$A2,""DNP/SUS/MIN"",""with ""&1233),1234)"
    myFormula2 = "INDEX(MLBtransactions!$A:$E,MATCH(1,(MLBtransactions!$D:$D=A$1)*(MLBtransactions!$A:$A<=$C2),0),5)"
    myFormula3 = "INDEX(MLBtransactions!$A:$E,MATCH(A$1,MLBtransactions!$D:$D,0),5)"
    myFormula4 = "IF(COUNTIFS(MLBstats!$B:$B,A$1,MLBstats!$A:$A,$A2)=1,""DNP/SUS/MIN"",""LEAVE BLANK"")"

    'Insert the formula
    With Sheets("Sheet1").Range("E2:AZ140").SpecialCells(xlCellTypeBlanks)
        .FormulaArray = myFormula1
        .Replace "1232", myFormula2
        .Replace "1233", myFormula3
        .Replace "1234", myFormula4
    End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

OK some progress. Many thanks. If I use that and set the range to H14:H16 and run it with H15 blank (H14 and H16 are non-blank), it enters the array formula into H15, but the addresses haven't incremented to match the cell. If I make H14 blank and the other 2 cells filled, same result, formula in but addresses still as written in the formula. If H16 is blank (H14 and H15 non-blank) I get the error "No cells were found" although H16 is empty and within the range.
The last bit ("no cells found" even though H16 is blank) is likely because your UsedRange is currently only extending to H15 - cells outside the current "used" area of the worksheet do not appear to be deemed to be part of the worksheet when using SpecialCells (I suspect that is a deliberate decision by Microsoft, and not a bug). For the first part, you are going to have to give us a lot more details as to what your worksheet looks like, and why you are only selecting some cells within the range to apply the formula to, before we can come up with any useful answer.
I'm only selecting small sections of my full table for testing (time) purposes. Because of its size it is faster to only test on a small piece of it. My data tracks injuries in Major League Baseball. Each row represents a team and one of their games. In the columns are each player in the league. The cells contain game data for each player. If a cell is blank, either the player didn't play in the game, or they are not on that team and would be missing from all rows for the games for that team.
1

Setting .FormulaArray to a range of cells in VBA is the same as pressing CTRL+SHIFT+ENTER while the entire range is selected. This is used when a single formula is returning an array of results and you want to display that array in the range of selected cells. This requires a contiguous range of cells to display the array. Trying to set .FormulaArray on a non-contiguous range of cells will fail, regardless of whether the formula is good or not.

I think you're trying to create an Array Formula that returns a single result after performing analysis on arrays; and you want this formula used in all blank cells.

In Excel, you would need to array-enter the formula into a single cell and then copy the formula into other cells.

Similarly, you need to do it in 2 steps in VBA. You need to first set the .FormulaArray for just one cell. This will also confirm that the formula is being constructed correctly in VBA. You can then copy that cell to all blank cells, using PasteSpecial if you only want to copy the formula.

Alternatively, you could loop through all blank cells setting the .FormulaArray individually, e.g.:

Dim raCell As Range

For Each raCell In Range("E2:AZ140")
    If IsEmpty(raCell) Then raCell.FormulaArray = ...
Next

However, as you have dynamic references, you would need to construct the FormulaArray carefully to correctly determine the formula required based on the .Row and .Column of the current raCell.

Copy and paste would be safer if you have an "achor" point. Somewhere you know you can always enter the exact same formula and get the correct result when copied and pasted to all other cells.

Improve this answer

5 Comments

I've tried the first option you mention. Entering the array formula outside the table, copying and pasting the formula into the table in all blank cells. The array formula is entered properly into the single cell and copied into all empty table cells well. But I lose the A$1 cell references which become #REF! in the resulting formulas. I don't use a lot of loops in my macros (don't know how). I'm wondering if it would be more straightforward
I actually meant try putting it straight into one of the cells in your table. You can paste it into the first blank cell using: Range("h14:h16").SpecialCells(xlCellTypeBlanks).cells(1) and then copy it from there.
That said, a loop is much safer. SpecialCells doesn't return blank cells after that last used cell on your spreadsheet, so may not return all cells you expect. I've added a simple For loop to get you started, but you should read up on them more.
That said, a loop is much slower. Using copy-paste will most likely be quicker. Also, if you don't know where your first blank cell will exist, that means you also don't know what the initial value for the formula needs to be before copying and pasting it. You may need to start by creating the formula in a cell you know will always be blank, but adjust it so that when it is copied into the table it has the correct references. You could determine this by working backwards: Put the correctly formula into a blank cell in a table, then copy and paste it to your always blank cell outside the table.
Thanks @Michael. Time shouldn't be an issue as the macro is replacing a manual step that takes me ~60 minutes. Even if the macro takes 120 minutes to complete it's time I don't have to spend doing it. I'll experiment with a loop. But I need to take the other issue of the error I was initially getting into consideration which might complicate the loop

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.