FormulaArray problem

[ClimoC]

Greetings

My FormulaArray is too long - the dreaded +255 characters limit coming into effect.

I have tried two different workarounds with mixed results.

First I tried splitting the formula (a long If, ISERROR, MATCH, INDEX function with multiple criteria) into two parts, declaring each part as a string, then placing the first part in the cell (with 'X-X-X' concatenated on the end) and running a .replace on the X-X-X with the second part. Didn't work. I never did find out why it didn't work, because when I declared the 2nd string as 300 letter 'A's, it did the replace fine - but when I tried putting the 2nd part of the formula in there, the macro ran with no problems but didn't do the replace. When I manually tried doing the replace, it gave me a very ambigious "Your formula has an error" or something stupid like that (and wouldn't paste and highlight the error).

The second method I tried was again setting parts as strings, and then using a Len(String1) +1 and then concatenating the second part on (something I found on another site). That seemed to work (kind of) except that instead of doing a series of match,index etc and returning the value I was after (a string of text from the pertinent cell in another workbook), all I got was "1420". Apparently, on their own, String 1 = 142 and string2 = 0. Don't know where it's getting that from

So I've decided the best thing to do is have the macro do all the work, and then just paste the values. Problem is, this is a bit too complex for me and I was wondering if someone could kindly assist me in turning this formulaarray into VBA.

The full formulaarray is:

"=IF(ISERROR(MATCH(1,([" & wb1 & "]" & s & "!R6C5:R100C5=RC9)*([" & wb1 & "]" & s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & s & "!R6C6:R100C6=RC10),0)),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15,MATCH(1,([" & wb1 & "]" & s & "!R6C5:R100C5=RC9)*([" & wb1 & "]" & s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & s & "!R6C6:R100C6=RC10),0)))"

'wb1' is a workbook.name string
's' is a worksheet.name string

The two tables that this is to move data between are identical. What is not quite identical are two tables used for the criteria. One is a complete table with all info, the other is a simplified version with the same data but fewer columns.

I'd also just like to point out that I'm certain this is how this needs to be done. The module containing this will be exported into 13 different workbooks, so that each of those workbooks can send information back to the master workbook.

One last thing, the formulaarray is being used in this procedure:

Set destr1 = Range("AA6:AA500")
For Each Cell In destr1
    If Cell.Offset(0, -22).Value = wb.ActiveSheet.Range("C6").Value Then
        With Cell
        .FormulaArray = "=IF(ISERROR(MATCH(1,([" & wb1 & "]" & s & "!R6C5:R100C5=RC9)*([" & wb1 & "]" & s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & s & "!R6C6:R100C6=RC10),0)),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15,MATCH(1,([" & wb1 & "]" & s & "!R6C5:R100C5=RC9)*([" & wb1 & "]" & s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & s & "!R6C6:R100C6=RC10),0)))"
            
        End With
    End If
Next

 

[Colin Legg]

Hi,

I expect the Range.Replace method failed because of the application's ReferenceStyle setting. If the replacement contents contains range references using R1C1 notation, then the ReferenceStyle needs to be R1C1 too.

This worked for me:

Sub test()
 
    Const strWHAT As String = "XXXX"
 
    Dim xlRef As XlReferenceStyle
    Dim strReplacement As String
    Dim wb1 As String
    Dim s As String
    wb1 = "Book12"
    s = "Sheet1"
 
    strReplacement = "MATCH(1,([" & wb1 & "]" & s & _
                        "!R6C5:R100C5=RC9)*([" & wb1 & "]" & _
                        s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & _
                        s & "!R6C6:R100C6=RC10),0)"
 
 
    With Sheet1.Range("K15")
        .FormulaArray = "=IF(ISERROR(" & _
                    strWHAT & _
                    "),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15," & _
                    strWHAT & _
                    "))"
 
        xlRef = Application.ReferenceStyle
        Application.ReferenceStyle = xlR1C1
 
        .Replace What:=strWHAT, _
                    Replacement:=strReplacement, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    MatchCase:=False, _
                    SearchFormat:=False
  
        Application.ReferenceStyle = xlRef
    End With
    
End Sub

Hope that helps...

 

[ClimoC]

You'll have to forgive the Kiwi in me when I say, Colin,

YOU BL00DY LEGEND!

Works like a dream! Now I just have to adjust this a few times over as it needs to be done on 10 columns lol

 

[ClimoC]

Hey Colin (or anyone else for that matter) - this worked fine for a few days, and then just recently, as I was moving this workbook from testing phase to implementation stage, I moved the workbook and renamed it too.

Now, Im back to having "Unable to set the FormulaArray property of the range class" when the only thing that has changed from when it did work is the filename and the path - but I updated those.

I just don't see what the problem is. To my eyes the macro looks fine, just like Colin's one above (with my own variables).

Can anyone see the problem? Thanks

Sub boomerang()
Application.ScreenUpdating = False
Const strWHAT As String = "XXXX"
 
Dim xlRef As XlReferenceStyle
Dim wb As Workbook
Dim wb1 As String
Dim master As Workbook
Dim sourcer As Range
Dim destr1, destr2, destr3, destr4, destr5, destr6, destr7, destr8, destr9, destr10 As Range
Dim c As Range
Dim r As Range
Dim s As String
Dim strReplacement As String
Dim string2 As String
Dim pt1, pt2 As Integer

Set wb = ThisWorkbook
wb1 = ThisWorkbook.Name
wb.Activate
s = ActiveSheet.Name
    'This defines the declaration "master" as the Master Spreadsheet. If the file is not open, it will open it based on the filepath below
    Set master = Workbooks("PushVOD Master Schedule.xls")
    If Err.Number > 0 Then Set master = Workbooks.Open(Filename:="Y:\Network Scheduling Hub\Push VOD\PushVOD Schedule\PushVOD Master Schedule.xls")
    If Not master Is Nothing Then master.Worksheets("Schedule").Activate Else MsgBox "File not found", vbInformation
master.Activate
Sheets("Schedule").Activate
strReplacement = "MATCH(1,([" & wb1 & "]" & s & _
                        "!R6C5:R100C5=RC9)*([" & wb1 & "]" & _
                        s & "!R6C9:R100C9=RC14)*([" & wb1 & "]" & _
                        s & "!R6C6:R100C6=RC10),0)"
'First column of the Promo info
Set destr1 = Range("AA6:AA1004")
For Each Cell In destr1
    If Cell.Offset(0, -22).Value = wb.ActiveSheet.Range("C6").Value Then  'does a check so it only does the calculation on rows that have the right matching Channel Group
        With Cell
        .FormulaArray = "=IF(ISERROR(" & strWHAT & "),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15," & strWHAT & ")"""")"
        End With
    End If
    With Cell
        xlRef = Application.ReferenceStyle
        Application.ReferenceStyle = xlR1C1
 
        .Replace What:=strWHAT, _
                    Replacement:=strReplacement, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    MatchCase:=False, _
                    SearchFormat:=False
  
        Application.ReferenceStyle = xlRef
    End With
Next
    
master.Activate
Sheets("Schedule").Activate

 

[Colin Legg]

Hi,

The version I posted:

.FormulaArray = "=IF(ISERROR(" & strWHAT & "),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15," & strWHAT & "))"

This puts the following, legal formula in the cell:
=IF(ISERROR(XXXX),"",INDEX([Book8]Sheet2!R6C15:R100C15,XXXX))
where wb1="Book8" and s ="Sheet2".

The version you've posted:

.FormulaArray = "=IF(ISERROR(" & strWHAT & "),"""",INDEX([" & wb1 & "]" & s & "!R6C15:R100C15," & strWHAT & ")"""")"

This tries to put the following, illegal formula in the cell:
=IF(ISERROR(XXXX),"",INDEX([Book8]Sheet2!R6C15:R100C15,XXXX)"")

Hope that helps...