0

I've been trying to input an array formula on a specific cell: I've tried A1 format with no success and R1C1 with a little more success. If I put Selection.FormulaR1C1 it works perfectly if I change that to Formula.Array it stops working:

R1C1:

Selection.FormulaR1C1 = "=LOOKUP(2,1/('Raw Data'!R11C" & TargetColumn & ":R1048576C" & TargetColumn & "=VLOOKUP(INDEX('Raw Data'!R" & TargetRow - j + 300 & "C1:R" & TargetRow - j & "C1,MATCH(MIN(ABS('Raw Data'!R" & TargetRow - j + 300 & "C" & TargetColumn & ":R" & TargetRow - j & "C" & TargetColumn & "-((R[1]C[2]-0.05)*R7C4))),ABS('Raw Data'!R" & TargetRow - j + 300 & "C" & TargetColumn & ":R" & TargetRow - j & "C" & TargetColumn & "-((R[1]C[2]-0.05)*R7C4)),0)),'Raw Data'!R11C1:R1048576C131,MATCH(""*""&""W""&R2C3&""*"",'Raw Data'!R5,0),FALSE)),'Raw Data'!R11C1:R1048576C1)"

A1:

Selection.Formula = "=LOOKUP(2,1/('Raw Data'!$" & TargetColumnLetter & "$11:$" & TargetColumnLetter & "$1048576=VLOOKUP(INDEX('Raw Data'!A" & TargetRow - j & ":A" & TargetRow - j + 300 & ",MATCH(MIN(ABS('Raw Data'!" & TargetColumnLetter & TargetRow - j & ":" & TargetColumnLetter & TargetRow - j + 300 & "-((D" & 20 + Rows2Copy & "-0.05)*$D$7))),ABS('Raw Data'!" & TargetColumn & TargetRow - j & ":" & TargetColumn & TargetRow - j + 300 & "-((D" & 20 + Rows2Copy & "-0.05)*$D$7)),0)),'Raw Data'!$A$11:$EA$1048576,MATCH(""*""&""W""&$C$2&""*"",'Raw Data'!$5:$5,0),FALSE)),'Raw Data'!$A$11:$A$1048576)"

None of them work with Selection.FormulaArray

If I try another formula lets say Selection.FormulaArray = "=SUM($F$12:$F$15)" it works perfectly. If I record the Marco and enter the formula in the spreadsheet, stop the Marco and copy that it won't work. Been stuck for 2 days on this trying to figure it out.

HackSlash
  • 4,944
  • 2
  • 18
  • 44
Andres Cobos
  • 43
  • 6
  • 1
    if you Debug.Print the formula what happens if you then copy/paste it into a worksheet? – Tim Williams Jul 17 '20 at 20:47
  • That is too long to evaluate. Break the problem down it to smaller parts. Evaluate each part separately. Join them together bit by bit. You should find the broken part along the way. – HackSlash Jul 17 '20 at 22:40
  • I think you need to use Selection.FormulaArray, instead of just Formula/FormulaR1C1. Use record macro to see what Excel does when you enter a formula array in a cell. Good luck. – JPortillo Jul 17 '20 at 22:49

1 Answers1

0

ArrayFormula has as 255 character limit, so that will never work. (Although you can try this solution if you want):

Overcoming the 255 char. limit for formulaArray in Excel VBA

Your formula is 550+ characters long (counting spaces). Microsoft says that the character limit of regular formulas is over 8000 characters, but... take that with a grain of salt.

Regardless, you will need to use a regular formualR1C1 or Formula to do the job here. debug.print is your friend in these cases. Throw a debug.print statement in and set a break point then check out the formula in detail in the immediate window. You will likely discover the error there.

Courtney Foster
  • 383
  • 2
  • 10
  • I didn't know it had a limit. I followed your advice, but even after splitting the formula, I haven't been able to run it, been testing it for the last 9 hours without success. – Andres Cobos Jul 20 '20 at 20:17