formulaarray

  1. 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 =...
  2. 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" & ",'" &...
  3. 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...
  4. 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...
  5. 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...
  6. 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"" &...
  7. 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...
  8. 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...
  9. 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...
  10. B

    VBA FormulaArray

    I am having some trouble with inserting an Array formula LR_Formula =...
  11. 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...
  12. 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...
  13. 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.. :(...
  14. 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...
  15. 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'...
  16. 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...
  17. 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>...
  18. 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'...
  19. 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...
  20. E

    FormulaArray Error - Unable to set the FormulaArray property of the range class

    Hi guys, Wondering if anyone can help me. I am getting an error: "Unable to set the FormulaArray property of the range class" when I try to run the following code: Range("AK3").FormulaArray =...

Some videos you may like

This Week's Hot Topics

Top