Matrix formule werkt niet via VBA

Status
Niet open voor verdere reacties.

Tweety1

Gebruiker
Lid geworden
6 mrt 2013
Berichten
637
Ik probeer een matrix formule in een cell te plaatsen via VBA alleen krijg ik een foutcode 438 (Deze eigenschap of methode wordt niet ondersteund door dit object).
De lengte van de formule is zoals ik het zie is kleiner dan 255 tekens.
Als ik de formule als "FormulaR1C1" plaats dan werkt ie wel maar is het geen matrix formule

Code:
Sub MatrixFormule()
Dim StrWs As String, StrC As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim strName As String

StrWs = "Jaar 1"

StrC = "X"
  Worksheets(StrWs).Name = StrC
  
 Range("I13").FormulaArray = "=IFERROR(IF(AND(VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0)="""",OR(" & StrC & "!R[-11]C[-3]=""Eerste kans""," & StrC & "!R[-11]C[-3]=""Herkansing""))," & StrC & "!R[-11]C[-3]&"" ""&INDEX(" & StrC & "!C[-3],LARGE(--(LEFT(" & StrC & "!R2C5:R[-11]C5,7)=""Cluster"")*ROW(" & StrC & "!R2C5:R[-11]C5),1)),TRIM(" & StrC & "!R[-11]C[-3]&"" ""&VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0))),"""")"
 'Range("I13").FormulaR1C1 = "=IFERROR(IF(AND(VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0)="""",OR(" & StrC & "!R[-11]C[-3]=""Eerste kans""," & StrC & "!R[-11]C[-3]=""Herkansing""))," & StrC & "!R[-11]C[-3]&"" ""&INDEX(" & StrC & "!C[-3],LARGE(--(LEFT(" & StrC & "!R2C5:R[-11]C5,7)=""Cluster"")*ROW(" & StrC & "!R2C5:R[-11]C5),1)),TRIM(" & StrC & "!R[-11]C[-3]&"" ""&VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0))),"""")"

  
  Worksheets(StrC).Name = StrWs
Application.Calculation = xlCalculationAutomatic
End Sub

Voorbeeld:

Bekijk bijlage Matrix.xlsm
mvg
Kasper
 

Bijlagen

  • Matrix.xlsm
    21 KB · Weergaven: 56
Laatst bewerkt:
Moet formulaArra niet FormulaArray zijn?
 
Klopt krijg dan foutmelding 1004 (Eigenschap FormulaArray van klasse Range kan niet worden ingesteld.
 
Heb je geprobeerd om de formule op te nemen?
Als die dan wel werkt heb je gelijk de goede code.

Dit heb ik voor mijn bestandje ook zo gedaan.
 
De formule voor vba in formulaarray is max 255 tekens.
 
Ik heb de aantal tekens geteld in de cell maar ik kom niet verder dan 237 (Tabblad naam "X" in plaats van "Jaar 1".
 
Zet de formule maar eens in cel A1 als tekst.

Code:
"=IFERROR(IF(AND(VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0)="""",OR(" & StrC & "!R[-11]C[-3]=""Eerste kans""," & StrC & "!R[-11]C[-3]=""Herkansing""))," & StrC & "!R[-11]C[-3]&"" ""&INDEX(" & StrC & "!C[-3],LARGE(--(LEFT(" & StrC & "!R2C5:R[-11]C5,7)=""Cluster"")*ROW(" & StrC & "!R2C5:R[-11]C5),1)),TRIM(" & StrC & "!R[-11]C[-3]&"" ""&VLOOKUP(" & StrC & "!R[-11]C[-4],Werkvorm,5,0))),"""")"

In A2: =lengte(A1) = 395
 
Ok. Dan wordt "StrC" dus meegeteld. Ik dacht dat hij keekt naar tekens van de formule in de cell.
Ik kan wel "StrC" veranderen in "S" en "Werkvorm" veranderen in W. Dan kom ik nog steeds boven de 255 uit. Namelijk 346.
Is er nog een andere oplossing?
 
Plaats het bestand eens met de formule, misschien dat het korter kan.

Edit: Excuus, staat al in je openingspost.
 
Je werkt met VBA.

Dan kun je met VBA veel eenvoudiger de gewenste output in Blad3!I14:I40 toveren.
Als je vertelt met welke redenering het moet is het zo gepiept.
Arrayformules zijn ook onnodig vertragend en worden steeds weer herberekend als er iets in het werkblad verandert.
Terwijl ik vermoed dat de gegevens in Blad3!I14:I40 daar slechts eenmalig geplaatst moeten worden.
 
De bedoeling is dat macro de gegevens van tabblad "X" kolom "F" met gegevens uit tabblad legende (verticaal zoeken kolom "E") samenvoegt. Maar staat er Eerste kans, First chance, Herkansing, Resit dan moet hij de tekst van tabblad "X" naast cluster / onderwijseenheid samenvoegen (terug rekent naar boven).

De uitkomst zoals het eruit moet zijn staat in blad 3.
De gegevens die geplaatst moeten worden zijn de cellen "I12:500"
 
Het lijkt de 'oplossing' voor onhandig gestruktureerde gegevens.

Ik zie geen verband tussen kolom F en de legenda.

Code:
Sub M_snb()
  sn = Blad1.UsedRange.Columns(5).Resize(, 2).Offset(2)
    
  For j = 1 To UBound(sn)
    If LCase(sn(j, 1)) = "cluster / onderwijseenheid" Then
      sn(j, 1) = sn(j, 2)
      c00 = sn(j, 1)
    ElseIf LCase(sn(j, 1)) = "deelonderwijseenheid" Then
      sn(j, 1) = c00 & " " & sn(j, 2)
    Else
      sn(j, 1) = sn(j, 2) & " " & sn(j, 1)
    End If
  Next

  Blad3.Cells(14, 9).Resize(UBound(sn)) = sn
End Sub
 
Ziet er goed uit alleen dient de uitkomst van de deelonderwijseenheid precies andersom "Herkansing ....."
Kolom F en legende is nu het zelfde maar dit is klein stukje eruit. Het kan namelijk afwijken.
 
.. alleen dient de uitkomst van de deelonderwijseenheid precies andersom "Herkansing ....."

Dan moet je de code even aanpassen.

Kolom F en legende is nu het zelfde maar dit is klein stukje eruit.
Partiële gegevens, partiële antwoorden.

Nogmaals: er is geen verband tussen kolom F en de legenda.
Waarom zou je in kolom F niet meteen de juiste gegevens zetten ?
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan