• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

VBA Worksheetfunction.SumIfs met loop

Status
Niet open voor verdere reacties.
C

Comida

Gebruiker
Lid geworden
27 jan 2017
Berichten
5
—ik zie dat het gisteren te laat was en dat ik dit in de verkeerde rubriek heb gepost. Kan deze vraag verplaatst worden?—

Beste mensen,

Ik loop even (eigenlijk al een paar uur) vast met een macro die ik in elkaar probeer te zetten. Daarom is mijn hoop nu op jullie gevestigd.
Ik probeer een worksheetfunction.sumifs met een loop te combineren, maar dat gaat helemaal niet goed.

Op het inputblad staat vanaf rij 2 een projectnaam. De 2 rijen daaronder betreffen resources die door het project gebruikt worden. Dan volgt op rij 5 weer een andere project met daaronder weer twee regels resources, enz... Kolom 3 t/m X? (dynamisch) bestaan uit kwartalen van jaren.

De bedoeling is om de resources die aan een project zijn toegewezen in een bepaald jaar (bijvoorbeeld 2018) op te tellen en op blad 'Tabel' bij het juiste project en jaartal neer te zetten. De lijst projecten op blad 'Input' heeft dezelfde volgorde als op tabblad 'Tabel'.

Ik ben tot deze code gekomen... (zie ook het voorbeeldbestand)


Code: 
Sub Test()

    Dim DateRange As Range
    Dim lastRowI As Long
    Dim lastColumnI As Long
        
    lastRowI = Worksheets("Input").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowJ = Worksheets("Tabel").Cells(Rows.Count, 2).End(xlUp).Row
    lastColumnI = Worksheets("Input").Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set DateRange = Worksheets("Input").Range("C1", Worksheets("Input").Cells(1, lastColumnI))
 
    For j = 3 To lastRowJ
        For i = 3 To lastRowI Step 3
    
    Worksheets("Tabel").Cells(i, 3).Value = _
        WorksheetFunction.SumIfs(Worksheets("Input").Range(Cells(j, 3), Cells(j + 1, lastColumI)), _
        DateRange, ">=" & Format$("01/01/2018", "dd mmm yyyy"), _
        DateRange, "<=" & Format$("31/12/2018", "dd mmm yyyy"))
    
        Next i
    Next j
    
End Sub

Als ik van het optelbereik een vast bereik maak (die gelijk is aan het criteriabereik) dan werkt het. Nu krijg ik een Application-defined or Object-defined error.

Kunnen jullie mij verder op weg helpen? Ik weet sowieso niet of ik in de goede richting zit, of dat ik beter een ander pad in kan slaan. :confused:
Groet,
Comida

Bekijk bijlage Voorbeeldbestand.xlsm
 
Laatst bewerkt:
De code in het bestand hoort niet bij het bestand (verkeerde bladnamen).

Code: 
Worksheets("Tabel").Range(Cells(j, 3), Cells(j + 1, lastColum[SIZE=5][COLOR=#ff0000]n[/COLOR][/SIZE]I)

Loop je code eens met F8 door, en zet 'option explicit' bovenaan, dan zie je meteen al een fout.

Zet onderstaande eens onder 'For i = 3 To lastRowI'
Code: 
msgbox Range(Cells(j, 3), Cells(j + 1, lastColumnI)).address
    msgbox DateRange.address

Maak eens een formule die hetzelfde zou moeten doen en kijk of het daar goed gaat.
Misschien kun je het nu zelf oplossen, anders graag een nieuw bestand met de juiste gegevens.
 
Laatst bewerkt:
Waarom niet direct vanuit VBA en waarom alleen 2018?

Code: 
Sub VenA()
  ar = Sheets("Input").Cells(1).CurrentRegion
  ar1 = Sheets("Tabel").Cells(2, 2).CurrentRegion.Offset(1, 1)
  For j = 3 To UBound(ar) - 1 Step 3
    For jj = 21 To UBound(ar, 2) - 1 Step 4
      ar1((j - 3) / 3 + 1, (jj - 21) / 4 + 1) = ar(j, jj) + ar(j, jj + 1) + ar(j, jj + 2) + ar(j, jj + 3) + ar(j + 1, jj) + ar(j + 1, jj + 1) + ar(j + 1, jj + 2) + ar(j + 1, jj + 3)
    Next jj
  Next j
  Sheets("Tabel").Cells(3, 3).Resize(UBound(ar1), UBound(ar1, 2)) = ar1
End Sub
 
in C3 en doorvoeren.
Code: 
=SOMPRODUCT((VERSCHUIVING(INDIRECT(ADRES(VERGELIJKEN($B3;Input!$A$1:$A$50;0);1;;;"input"));1;2;1;59))*(JAAR(Input!$C$1:$BI$1)=C$2))

Een minder volatile formule.
Code: 
=SOMPRODUCT((VERSCHUIVING(INDEX(Input!$A$1:$A$50;VERGELIJKEN($B3;Input!$A$1:$A$50;0);1);1;2;2;59))*(JAAR(Input!$C$1:$BI$1)=C$2))
 
Laatst bewerkt:
@HSV,
Lekker bestand om wat mee te testen. De oktober maanden staan er als tekst in. Ik heb een paar verschillen tussen jouw formule en mijn macro. Waarschijnlijk moet het zo zijn.
Code: 
=SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(MATCH($B3;Input!$A$1:$A$50;0);1;;;"input"));1;2;[COLOR="#FF0000"]2[/COLOR];59))*(YEAR(Input!$C$1:$BI$1)=C$2))
En anders klopt mijn macro niet.:d
 
Die 2 zal waarschijnlijk wel goed zijn.
 
Beste HSV en VenA,

Excuus voor de fouten in het voorbeeldbestand. Bij het onherkenbaar maken van de gegevens ben ik kennelijk niet zorgvuldig geweest. :eek: Dat het 3de kwartaal als tekst erin staat, was me bovendien nog niet eens opgevallen. Het betreft een export uit een systeem, dus het is wel goed om te weten.

De oplossing van VenA werkt prachtig, ontzettend bedankt! Met een aantal functies daarin ben ik nog niet bekend, dus dat ga ik eens goed nalezen. Met de tips van HSV ga ik ook aan de slag. Bedankt!

Groet,
Comida
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan