Formule =INDEX( ...) in VBA

[jansm]

Heren, ik heb een tabel met daarin een tabblad "Trans" met transacties. In tabblad "Overzicht" wil ik mbv gegevens in A en C het saldo uit tabblad "Trans". Dat doe ik met de INDEX-functie. Deze formule staat in kolom C. Nu wil ik dat met een macro proberen te doen (kolom D). En dat lukt me niet. Ik denk dat het probleem de INDEX functie is die in de cel afgesloten moet worden met shft-ctr-enter. Het lukt me alleen om de formule als tekst in kolom D te krijgen maar dat is de bedoeling uiteraard niet.

Bekijk bijlage Test_Ophaal.xlsm

 

[Timshel]

Sub Ophalen()
    With Sheets("Overzicht")
        .Columns(4).Locked = False
        .Range("D2").FormulaArray = "=INDEX(TRANS!$G$2:$G$1001,MATCH(B2,IF(TRANS!$A$2:$A$1001<=A2,TRANS!$D$2:$D$1001),0))"
        .Range("D2").AutoFill Destination:=Range("D2:D" & .Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
        .Columns(4).Locked = True
    End With
End Sub

Een paar opmerkingen.
-Het is niet erg gebruikelijk om met behulp van VBA formules te maken. Maar misschien ben je aan het experimenteren.
-Waar je in werkbladformules de puntkomma gebruikt, dien je die in VBA in de regel te vervangen door komma's.
-Het (un-)locken van cellen heeft geen nut als het werkblad niet beveiligd is. Als het werkblad wèl beveiligd is faalt de code in deze vorm.

 

[jkpieterse]

Met een hulpkolom naast je data en een draaitabel...

 

[jkpieterse]

NB: Deze versie geeft het saldo na de EERSTE transactie op iedere datum. wil je de laatste transactie, verander dan de formule naar:

=COUNTIFS(D2:$D$50;D2;A2:$A$50;A2)

 

[jansm]

Heren, alvast bedankt voor jullie antwoorden/oplossingen. Ik ga er vanavond naar kijken.