Gemiddelde berekenen en Kopiëren naar sheet

[wdelangen]

Hallo Iedereen,

Ik heb 3 worksheets: index, gegevens1 en gegevens2. In sheet 'gegevens1' en sheet 'gegevens2' staat een kolom (A1 tot A10 met soms lege rijen ertussen) met cijfers. Graag wil ik dat het gemiddelde van 'gegevens1' en 'gegevens2' berekend wordt, en geplaatst wordt in de index sheet op een vast cell locatie, bijvoorbeeld in cell A1 staat gemiddelde van sheet 'gegevens1' en in cell A2 staat het gemiddelde van sheet 'gegevens2'

Ik heb het volgende gevonden om het gemiddelde te bereken:

Dim lastcellrow, i As Integer
For i = 1 To 3 'for columns A to D
lastcellrow = Cells(30, i).End(xlUp).Row ' finds last row in col
If lastcellrow > 2 Then
Cells(lastcellrow + 1, i).Formula = "=AVERAGE(R[-" & lastcellrow - 1 & "]C:R[-1]C)"
End If
Next i

Dat lukt, alleen als ik eerst de data uit 'gegevens1' en 'gegevens2' kopieer naar sheet 'index'. Maar dan moet ik alles weer opschonen en dat is onhandig.

Iemand een idee hoe dit ik dit kan doen?

Groet,

Willem

 

[etri]

Dag Willem,

Waarom gebruik je niet direct de AVERAGE formule in Excel (dus buiten VBA om)?

Ik zou het in VBA als volgt doen:

Public Sub Gemiddelde()
    Dim lastcellrow As Integer, i As Integer
    
    Sheets(2).Activate
    For i = 1 To 3 'for columns A to C
        lastcellrow = Sheets("Sheet1").Cells(30, i).End(xlUp).Row ' finds last row in col
        If lastcellrow > 2 Then
            Cells(1, i).Formula = "=AVERAGE(Sheet1!R" & 1 & "C" & i & ":Sheet1!R" & lastcellrow & "C" & i & ")"  
        End If
    Next i
    
End Sub

In bovenstaande code is de cellverwijzing absolute, wat m.i. wat veiliger en duidelijker is (maar dat is smaakgevoelig...)
Door in de formule de naam van de sheet ervoor te zetten met een uitroepteken (in de code"Sheet1!") kun je verwijzen naar een andere sheet. In jouw geval zou dat bijvoorbeeld worden:

Cells(1, i).Formula = "=AVERAGE(Gegevens1!R" & 1 & "C" & i & ":Gegevens1!R" & lastcellrow & "C" & i & ")"

Ik hoop dat je hier wat mee kunt... En dat je vraag nog steeds open staat...

 

[wdelangen]

Hallo Etri,

Bedankt alvast voor je reactie! Tijdens het uitvoeren van het script krijg ik een Excel pop up met update Values: Data. Dit wordt drie keer gedaan. Enig idee waarom?

Ik heb al in de code:

lastcellrow = Sheets("Sheet1").Cells(30, i).End(xlUp).Row ' finds last row in col

Gewijzigd naar

lastcellrow = Sheets("Gegevens1").Cells(30, i).End(xlUp).Row ' finds last row in col

Maar dan kijg ik nog steeds een Excel popup.


Groet,

Willem

 

[mcs51mc]

Waarom gebruik je niet direct de AVERAGE formule in Excel (dus buiten VBA om)?

Omdat hij misschien een Nederlandse versie heeft van Excel, en dan gaat "Average" niet :shocked:

Voor de rest ben ik 200% met jouw akkoord, VBA heb je hiervoor niet nodig.
Gewoon
"=GEMIDDELDE('Gegevens 1'!A1:A10)" in cel A1 van de "Index" sheet en
"=GEMIDDELDE('Gegevens 2'!A1:A10)" in cel A2 van de "Index" sheet en de kous is af tenzij... ... ... ... ...je een welbepaalde reden hebt om het toch via VBA te doen.


Dan stel ik mij de vraag, "Wie zal die macro starten?"
Moet er dan nog een knop voorzien worden of wens je dat de macro uitgevoerd wordt als er iets wijzigt aan die cellen in "Gegevens 1" en "Gegevens 2".
Wens je echt een macro oplossing, dan graag een beetje meer uitleg