0

yesterday I posted about this problem because I didn't know an array had a limit of 255 characters. Today I've been trying for the last 9 hours to break it down as shown here:

Overcoming the 255 char. limit for formulaArray in Excel VBA and on google search for other posts doing exactly the same thing.

I followed the instructions about in order for the trick to work, you need to write complete formulas, but still haven't figured it out.

    Dim TR As Double
    Dim TC As Double
    Dim Formula1 As String
    Dim Formula2 As String
    Dim Formula3 As String
    Dim Rows2Copy As Integer
    Dim PValue As String
    Dim str As String

str = OpenBook.Sheets(1).Cells(2, 1).Value
PValue = Mid(str, 20, 2)

Rows2Copy = (PValue / 5) - 10
TR = ActiveWorkbook.Sheets(2).Cells(22 + Rows2Copy, 2).Value
TC = WorksheetFunction.Match("*" & "W" & Cells(2, 3) & "*", Worksheets(1).Range("5:5"), 0)

    For j = 300 To TotalRows * 300 Step 300
       With ActiveWorkbook.Sheets(2).Range("B24")            
                
          Formula1 = "=LOOKUP(2,1/('Raw Data'!R11C" & TC & ":R1048576C" & TC & "=VLOOKUP(INDEX('Raw Data'!R" & TR - j & "C1:R" & TR - j + 300 & "C1,MATCH(MIN(+FX2),+FX2,0)),'Raw Data'!R11C1:R1048576C131,+FX3,FALSE)),'Raw Data'!R11C1:R1048576C1)"
          Formula2 = "ABS('Raw Data'!R" & TR - j & "C" & TC & ":R" & TR - j + 300 & "C" & TC & "-((R[1]C[2]-0.05)*R7C4))"
          Formula3 = "MATCH(""*""&""W""&R2C3&""*"",'Raw Data'!R5,0)"
                
         .FormulaArray = Formula1
         .Replace "+FX2", Formula2, xlPart
         .Replace "+FX3", Formula3, xlPart
      End With
    Next j

Sample

In the Picture, the Load case is extracted as well as the maximum force applied, the first thing is based on W Tested, it looks for the column containing that data, then returns the maximum value on "Applied Load" Column and finally returns the ID of that value. The applied load value represents 75.7% The next step is based on a 300 upward row range from the starting position (in this case row 44834) find the closest match to 70% of the initial test and then return the value, then with 65% then 60% and so on until 10%. Everything else will populate with those values and create the intended charts. All I'm missing is this formula for it to work

Thank you! I really appreciate the help!

Andres Cobos
  • 43
  • 6
  • 2
    debug.print the first formula. Copy and paste into Excel, does it work? – Scott Craner Jul 20 '20 at 20:33
  • I too have had to do this before and you must be very strategic about how your write the formulas to substitute into one another. What errors, if any, do you get and which lines of code cause the error? – ArcherBird Jul 20 '20 at 20:42
  • 1
    Making a UDF in VBA might be one way to reduce that size of your formulas. https://www.excelcampus.com/vba/user-defined-functions-explained/ – HackSlash Jul 20 '20 at 20:49
  • Some sample data (or a mock workbook) and a detailed description of what you are trying to accomplish would help. Your code snippet is incomplete. It is hard to provide a complete answer if we cannot recreate the problem. – TinMan Jul 20 '20 at 21:32
  • A named range may be enough to get you under the 255 character limit. It appears that `R" & TR - j + 300 & "C` is an absolute reference but there is no way of telling without knowing how `TR` is calculated. – TinMan Jul 20 '20 at 21:36
  • First, thank you all for your time to try to help me. I have a datasheet of over 120,000 lines of data of a mechanical test of an airplane. 4 tests (W1, W2, W3 & W4) depending on the location of the applied force. First I need W column depending on that extract the maximum value and then return the ID (Column to the very left) of that specific point then, within a 300 range upward the row of the previous value locate the closest match of a difference of 5% in applied force. The first value for example is 75% the next closest match would be 70% and then return the ID of that value. – Andres Cobos Jul 21 '20 at 17:57
  • Already tried renaming ranges, TC & TR where initially TargetRow & TargetColumn Didn't work with debug.print, the "+FX" values stay like that I didn't get any errors, the code just stops after that line without doing anything else and without pasting the formula – Andres Cobos Jul 21 '20 at 18:11

2 Answers2

0

Maybe this doesn't help, but I hope it gives you some insight.

Excel charts work best when they are charting data from a worksheet. If you can define an array in VBA, you can easily dump that array into a sheet somewhere, then insert that range into the chart series formula. This eliminates the problem with array character limits, and makes it easier to debug the chart data.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27
0

I managed to solve it but thanks to everyone anyways:

TR = ActiveWorkbook.Sheets(2).Cells(22 + Rows2Copy, 2).Value
TC = WorksheetFunction.Match("*" & "W" & Cells(2, 3) & "*", 
Worksheets(1).Range("5:5"), 0)
TCL = Split(Cells(1, TC).Address, "$")(1)

TotalRows = (PValue / 5) - 3
ActiveWorkbook.Sheets(2).Activate
        
Rows2Copy2 = Rows2Copy

        For i = 0 To TotalRows Step 1
            
            With ActiveWorkbook.Sheets(2).Cells(19 + Rows2Copy - i, 2)
                   
            'Split formula
            Formula1 = "=LOOKUP(2,1/('Raw Data'!$" & TCL & TR - PRange & ":" & TCL & TR & "=VLOOKUP(INDEX('Raw Data'!A" & TR - PRange & ":A" & TR & ",MATCH(MIN(+FX2),+FX2,0)),'Raw Data'!$A" & TR - PRange & ":EA" & TR & ",+FX3,FALSE)),'Raw Data'!$A" & TR - PRange & ":A" & TR & ")"
            Formula2 = "ABS('Raw Data'!" & TCL & TR - PRange & ":" & TCL & TR & "-((D" & 20 + Rows2Copy2 & "-0.05)*$D$7))"
            Formula3 = "MATCH(""*""&""W""&$C$2&""*"",'Raw Data'!$5:$5,0)"
                           
            .FormulaArray = Formula1
            .Replace "+FX2", Formula2, lookat:=xlPart
            .Replace "+FX3", Formula3, lookat:=xlPart
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter

            End With
            Rows2Copy2 = Rows2Copy2 - 1
        Next i
Andres Cobos
  • 43
  • 6