formula array

Keep getting the unable to set the FormulaArray property of the Range class error. I've tested the formula by manually adding to a cell and it works fine. But, I can't seem to find what the issue is. Any ideas on what is tripping this up? Sub Array () Dim theFormulaPart1 As String Dim...

I've got some VBA code that populates an array with different formulas as text. The array is then written to a table. For i = 1 To UBound(array1) array1(i, 2) = "=INDEX(tblMCC_4[In],MATCH([@Med],tblMCC_4[Med],0))" array1(i, 3) = "" array1(i, 4) = ""...

Hello, I have the following formula: =IFERROR(INDEX(Analysis!$B$1:$B$278, SMALL(IF(Analysis!$A$1:$A$278=$C$44, ROW(Analysis!B1:B278),""), ROW()-44)),"") In cell C44 I have text that I want to find. X*XXXXXXXXXXXXX I want to find the match for this but I need to make the second letter in...

Hi Folks, I have this formula that finds one column of matches but I need a second column to also be returned. I need help in fixing this formula to allow the second column of matched info to be listed in column D if possible. Thanks Formula: =IF(ISERROR(INDEX('Analysis...

Hello! I've been lurking the message board for several weeks and it's been very helpful as I slowly learn more about VBA. I've read multiple posts on how to incorporate a string into a Formula Array function, but after being stumped for several days, I think I need another pair of eyes to take...

Hello there and I require some help to build an excel formula. Situation: I have a column A with names and a column B with dates. In my table there are entries with the exact same name, but of those with different entry dates. I want in column C to identify for each row name duplicates and...

Good Day, I'm trying to figure out how to pull data from my raw data page (Screenshot 1) and transfer certain data to the second spreadsheet (Screenshot 2). I'm having troubles with getting my formula to populate the correct run number. I'm trying to use Index match function. If I select...

Hello! I'm trying to create a formula to categorize an actual report's records, based on another pre-based estimated columns. (pic below) My conception is, to find the right channel group to cell i5 and below, based upon the actual report's channel, date and the closest time compared to the...

Hi, I am looking to copy 4 cells of formulas down one row. One of the formulas is an array. What I came up with copies the formulas down but they still reference the cell from the line above. I would like to add a border to the active row B:H at the end. <code style="font-family: "Courier New"...

I have a set of data which I need to sort based on some relatively simple criteria. The difficulty I am having is how to capture the interdepencies of the logic of the sort criteria. Written in plain English, I am trying to do the following. -If a record has an email address, categorise as...

Hi everyone, Ive been having some trouble with an array formula that's over 255 characters, and ive been trying to split it up into sub/dummy functions, but it doesnt seem to be working. Can someone please take a look at my code and see if you spot anything? Sub LongArray_ProvincialCalcs()...

Hello all; new to the board. I'm really stuck on a problem. I'm trying to write a formula that will be used in conditional formatting. I have a calendar that uses macros and self populates the days when selecting a year using a spin button. I want to conditional format each day to change color...

I have a table with golf scores for a junior golf tournament with the following columns: name (A2:A101), age (B2:B101), round 1 score (C2:C101), round 2 score (D2:D101), and round 3 score (E2:E101). All golfers participated in rounds 1 and 2 and half the field competed in round 3. The ages...

Hi, I have another long formula array. A1 notation style: =IF($C2="New...

Hye, Been trying to apply solution posted on the net for my long array formula. But to no avail. Just don't know which part is wrong..Please help to correct. Sub MyLongArray() Dim theFormulaPart1 As String Dim theFormulaPart2 As String LRow = ActiveSheet.Range("A1"...

Hello, I'm having trouble with my index formula.. Situation: First table is data, second table is calculation column A B C D Table range is A2:D6 <tbody> GSSE GTRS D 70417 50 100 D 160784 150 200 C 70417 250 300 C 160784 350 400...

Hi Friends, I need to find max and min from a given range based on criteria. Input and Output format is shown below. Input: <tbody> A B 1 Code Number 2 01 1234 3 01 3445 4 01 566 5 01 66634 6 02 45678 7 02 33 8 02 456 9 03 3456 10 03 23 11 03 456 12 03 678 13 03...

I have an Excel table named "WT_DARK_STTC", with columns titled "STTC", "Tissue", and "Unit Distance", in addition to others. On another sheet, there is another table named "STTC", and I am trying to set the column formula of its fifth column. The formula I wanted is essentially a MEDIANIFS()...

Hi I have a criteria in cell B2 and have a table from A8:I22. I write formula on column C when the Column F and I is Zero, show it Empty. I filtered data based on column C (Header Name = Year) and Hide table Rows based empty cells on Column C. I Want when I change Criteria on cell B2 and change...

I need to count clients served each day. I want to only count a client one time even if they have multiple services in one day. I have a formula that works in Excel I have Column A is a date field and Column B is Client ID =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) <tbody> Date...