formulaarray

  1. U

    VBA FormulaArray Syntax/AutoFill/Set Ranges

    Hi all I am trying to do a few things here with VBA: Here's the source Worksheet named MoCMatrix ABCDEFGHIJKLMNOPQRSTUVW1HELPER ROW > Structural Integrity-MoCStructural Integrity-Substantiation DocumentStructural Integrity-StatementFlammability & Smoke-MoCFlammability & Smoke-Substantiation...
  2. H

    VBA Formula Array

    Hello, I am trying to input below formula array into a cell M11, it returns me an error massage "Unable to set the FormulaArray property of the Range class": lastrow_AFGR2 = Sheets("AFGR").Range("D:D").Find(what:="*", searchdirection:=xlPrevious).Row Range("M11").FormulaArray =...
  3. N

    unable to set formulaarray property of the range class

    Hello, I'm having issues with inputting a formula array into a range of cells. The code in question is as follows: theformulapart1 = "=IFNA(INDEX('" & ws & "'!$" & AllelCol & "$1:$" & AllelCol & "$1000" & "X_X_X())" theformulapart2 = ",MATCH('Allele summary'!$C" & L & "&" & CL & "3" & ",'" &...
  4. S

    .FormulaArray - Missing End Curly Bracket - Normal?

    So I noticed that when I use .FormulaArray to input an in-cell array formula, in the formula bar I see the formula starts with a curly bracket "{". But I don't see one on the end. Normally when I input an array formula via CSE, there is one at the start and one at the end, to identify that it...
  5. S

    Why do I get this VBA error message?

    I have a table ("Fruit") in sheet "Fruit" which is comprised of the header (row 1) and then a single row in the body. The columns are: Apples Bananas Total Fruit I have no data in row 1, it is simply blank. If I input the following code into a vba module, it works fine. Sub Fruit() Dim...
  6. C

    (VBA) FormulaArray Character Limit

    I'm running into some problems with the character limit on the .FormulaArray property in vba. I've seen some workarounds but as far as I can tell, none of them would apply to my situation. The workbook I'm working on is a model that, in part, imports data from another workbook. The formula...
  7. E

    .FormulaArray in VBA does not work

    Hi I try to past an Array Formula into one single cell, but the code stops at that line and I really cannot find the error. This is my formula: ' Formula for calculating number of days in interest period... .Range("I4").FormulaArray = "=IF($N$1=360,IF(C4<>0,C4-INDIRECT(""C"" &...
  8. M

    Error:Unable to Set The FormulaArray Property of the Range Class

    Hi All, I know this topic has been covered in great length but was not able to find anything that resolves my issue. I have a piece of code that is a FormulaArray but I am receiving the infamous error: "Unable to Set The FormulaArray Property of the Range Class" When I switch it from...
  9. P

    help with array formula

    Hi I would like to add a second "logical test" to the IF statement to add YELLOW. so IF(OR(RED=b3:b10, YELLOW = b3:b10 I've tried SMALL(IF(OR but couldn't get that too work =INDEX($C$3:$C$10, SMALL(IF("Red"=$B$3:$B$10, ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1, ""), ROW(A2))) Much appreciated...
  10. J

    VBA FormulaArray Runtime Error

    Hi all, Have been stucked in a formula for hours now, cant really figure out what i'm doing wrong in this formula, maybe several mistakes by me or maybe something little hat i just missed: Range("T" & rng.Row).Select Selection.FormulaArray = "=IFERROR(INDEX(Tabell_Tidsrapport.accdb15[Effektiv...
  11. B

    VBA FormulaArray

    I am having some trouble with inserting an Array formula LR_Formula =...
  12. G

    FormulaArray in Excel Macro

    Hi all, I have been referring to this forum quite a fair bit recently to learn about Excel Macro from fellow experts in here. And for that, I want to thank you all first. I have also referred some threads related FormulaArray too... which comes to the main reason of this thread. I know that...
  13. L

    Applying array formula to range VBA

    Hello, I am trying to apply the following array formula to a range in Excel: =IFERROR(INDEX(AllFiles!A$2:A$1000000,SMALL(IF(AllFiles!$C$2:$C$1000000=$A$2,ROW(AllFiles!A$2:A$1000000)-ROW(AllFiles!A$2)+1),ROWS(AllFiles!A$2:AllFiles!A2))),"") using the following methods that I found online (I'm...
  14. D

    How to make Long FormulaArray macro to work? :(

    Dear Excel Expertise, I had an error which I googled showing the meaning is that my formula is too long (>255 chars). I tried to cut them into lines like below but I still got the same error. Can anybody be kind enough to correct my formula, please? I have tried to troubleshoot for hours.. :(...
  15. C

    How to combine FormulaArray and FormulaLocal options in Excel VBA?

    I am sending functions inside VBA code to the cells with a VBA-code line like below: Sheets("Sheet1").Range("B2").FormulaLocal ="=somefunction_in_local_language" I am using <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida...
  16. J

    Unable to set the ForumulaArray property of the Range class help

    Hi I am trying to insert formula to my cell by vba it compaers two cells and then calculates third value which has same values .Range("K" & RowNumber).FormulaArray = "=SUM((F5:F1535)*(C5:C1535=C' [" & RowNumber & "]')*(B5:B1535=B' [" & RowNumber & "]'))" But i get the following error '1004'...
  17. D

    Merging Long FormulaArray in VBA

    Hi there, I've been trying to use the method showed in Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA this whole day to overcome the error "Unable To Set The Formulaarray Property Of The Range Class - Error 1004" but still to no avail. I just don't know what else I've...
  18. U

    Trouble with getting CountIFS to work in FormulaArray

    Thank you for all those have been contribution to the forum. I have been using it for a while and finding many solutions for my problems. This latest one I couldn't find a solution any where. I have this example data set: <tbody> B G <tbody> Notification </tbody> <tbody>...
  19. R

    Referencing set variables and ranges in array formula

    Hello, I'm having a problem referencing set variables and ranges in my formula: Selection.FormulaArray = _ "=AVERAGE(IF(MONTH(Data!C2:C2315)=11,Data!X2:X2315))" This works fine but I would like it to be dynamic as the data range and condition changes. I would like the '=11'...
  20. Y

    Unable to set the FormulaArray property of the Range class Error

    I hope someone can help me with this. When I manually enter this array formula into a cell using Ctrl-Shift-Enter, everything works fine: =IF(ISNA(INDEX(Log_WK!A:A;MATCH(SLA!B5;IF(VLOOKUP(E5; Correspondance!A2:B173; 2...

Watch MrExcel Video

This Week's Hot Topics

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
Top