VBA - Zoek formule plakken in bereik - negeren regels met subtotalen

[gjanus]

Hallo,

Kan iemand mij helpen met een formule plakken via VBA maar daarin moeten regels genegeerd worden waar subtotaal formule staan.
Ik hoop dat voorbeeld bestand de bedoeling duidelijk maakt.

Werking praktijk:
Tabblad actuals: Geboekte uren word gedumpt/opgehaald met query
Tabblad forecast: Door de index formule worden de huidige actuals zichtbaar
Tabblad forecast: Forecast word daarna aangepast (handmatig opgehoogd/verlaagd - verder in de tijd uitgezet)
Tabblad forecast gaat weer terug het systeem in als nieuwe forecast

Ik zou graag willen dat via VBA de index formule (via een knop o.i.d.) geplakt wordt op alleen de regels zonder subtotalen zodat deze intact blijven.

Is dit mogelijk?




Alvast bedankt.

 

[HSV]

Bv.

Sub hsv()
Dim sv, sv_2, i As Long, ii As Long, j As Long
sv = Sheets("actuals").Range("c3:i6")
With Sheets("forecast")
 sv_2 = .Range("c4:i10").Formula
    For i = 1 To UBound(sv)
      For ii = 1 To UBound(sv_2)
        If sv(i, 1) = sv_2(ii, 1) Then
           For j = 2 To 7
              sv_2(ii, j) = sv(i, j)
           Next j
        End If
      Next ii
    Next i
 .Cells(4, 3).Resize(UBound(sv_2), 7) = sv_2
End With
End Sub

 

[gjanus]

Hij werkt, bedankt Harry,

Is het mogelijk ook de index formule in stand te houden? Ik gebruik namelijk in het werkelijk bestand een formule die obv gegevensvalidatie ook naar nog een ander tabblad (baseline) kan kijken.

 

[snb]

Geen idee of ik het begrijp, omdat het gewenste eindresultaat ontbreekt:

Sub M_snb()
  For Each it In Sheet1.Cells(3, 2).CurrentRegion.Offset(2, 1).Resize(, 1).SpecialCells(2).Areas
    it.Offset(, 1).Resize(, 6).Value = it.Offset(, 1).Resize(, 6).Value
  Next
End Sub

 

[HSV]

Hij werkt, bedankt Harry,

Is het mogelijk ook de index formule in stand te houden?

Die Index formule van je klopt van geen kant.
Ze geven verkeerde waardes retour doordat je de index steeds laat verschuiven.

 

[snb]

Ben je hier niet een draaitabel aan het heruitvinden ?

 

[HSV]

Dat heeft er alle schijn van @snb.

 

[gjanus]

Ben je hier niet een draaitabel aan het heruitvinden ?

Ik snap wat je bedoeld.
Werkelijk bestand is wat lastig na te bootsen, deze staat namelijk vol met persoonsgegevens en queries naar onze warehouse, maar bijgaand een nieuwe poging.

Die Index formule van je klopt van geen kant.
Ze geven verkeerde waardes retour doordat je de index steeds laat verschuiven.

Excuus niet goed gecontroleerd - normale formule die ik gebruik is draaitabel ophalen.

Poging 2 (zie bestand poging 2):

Tab Query
Deze bestaat normaal niet - normaal rechtstreeks verbonden met datamodel

Tab draaitabel
Draaitabel van de Planned value (PV), Actuals (AC) en Forecast (FC) die dus met een query vanuit systeem ingelezen worden.

Tab Overzicht
Hier word de forecast (waar nodig) handmatig aangepast.
Waarom deze opbouw?- ons ERP systeem kan nieuwe forecast inlezen met wat vaste kolommen. De kolommen in de tijd moeten de maandag van de week weergeven (dat terzijde voor de formule maar wel de rede van deze opbouw).

Ik heb ervoor gekozen dat in cel e1 de waarde van de week gekozen kan worden en in cel f1 een paar "doorkijken" die je inderdaad normaal in een draaitabel doet. - belangrijkste weergave = actuals-forecast - deze laat als datum in het verleden ligt de actuals zien, datum in de toekomst de forecast. Vanuit hier plannen we verder.

Waarom?
De forecast moet aangepast kunnen worden.
Stel we pakken de actuels (cel e1) tm week 5 - dan geeft overzicht weer: Actuals tm week 5, forecast vanaf week 6

Als we dan de forecast wil aanpassen veranderen we de forecast en druk op knop opslaan. - er word dan een bestandje aangemaakt die in het ERP ingelezen kan worden.
Hierna kwestie van bestandje inlezen in ERP en draaitabel vernieuwen zodat deze de nieuwe forecast actueel is. In overzicht laat ik dan met een macro de formule terugzetten zodat deze de actuele forecast ophaalt.
(werkelijke formules eventueel te vinden in namen).

Dit werkt op zich prima maar hebben graag subtotalen per WBS - De macro uit vorige regel plakt alleen de formule over het bereik heen, dus ook over de regels met subtotalen. Ik zou graag zien dat hij die subtotalen negeert.

Groet Jan

 

[gjanus]

Zou misschien iemand naar poging 2 willen kijken?

 

[cow18]

als je een kolom toevoegt in je gegevens, kan je draaitabel toch de juiste uitkomst geven.