Somme Si cellules visibles VBA

Le 24/02/2021 à 14:26

Bonjour,

Dans une macro, je souhaiterai mettre dans une cellule F15 de la feuille nommée Feuil 6; la somme des cellules visibles de la colonne I de la feuille Items. Le fait que cela soit visible est le résultat d'une macro déjà écrite, et qui gère, entre autres, cette action, dont ci-dessous le code:

'Add a new purchase
Private Sub Nouveau_Click()
i = Feuil12.Cells(3, 2)
nb_line = Feuil2.ListObjects("suivi_tab").Range.Rows.Count
i = i + 1
Feuil2.Cells((7 + nb_line), 1).Value = i
Feuil12.Cells(3, 2).Value = i


End Sub

Sub my_purchase_followup()

r = ActiveCell.Row
c = ActiveCell.Column
If (r < 8 Or c > 1) Then Exit Sub
Application.ScreenUpdating = False
Feuil2.Range("A7", "BU10000").Interior.ColorIndex = 0
Feuil12.Cells(1, 2) = r
Range(Cells(r, 1), Cells(r, 73)).Interior.Color = RGB(0, 155, 164)
Application.ScreenUpdating = True
f = ActiveCell.Value
Feuil12.Cells(2, 2) = f
Feuil13.Range("A5").AutoFilter field:=1, Criteria1:=f, VisibleDropDown:=False
'get number of item for current purchase
nb_item = Feuil13.ListObjects("items_tab").Range.Rows.Count
nb_selected_item = Feuil13.ListObjects("items_tab").Range.Resize(, 1).SpecialCells(xlCellTypeVisible).Count

'delete previous PR items
previous_selected_item = Feuil12.Cells(5, 2)
Feuil3.Rows(14 & ":" & 14 + (previous_selected_item - 1)).Delete
'delete previous EQR items
Feuil4.Rows(12 & ":" & 12 + (previous_selected_item - 1)).Delete

'delete previous CBA items
Feuil5.Rows(22 & ":" & 22 + (previous_selected_item - 1)).Delete

'delete previous PO items
Feuil7.Rows(31 & ":" & 31 + (previous_selected_item - 1)).Delete

'delete previous GRN items
Feuil8.Rows(11 & ":" & 11 + (previous_selected_item - 1)).Delete

'insert PR items
Feuil3.Rows(14 & ":" & 14 + (nb_selected_item - 1)).Insert
Feuil12.Cells(5, 2) = nb_selected_item
Feuil13.Range("C5", "K" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil3.Range("A14")
'if Akf then signataire; else tunis; else BGz, test à faire sur cellule+nb_selected Items.



'insert EQR items
Feuil4.Rows(12 & ":" & 12 + (nb_selected_item - 1)).Insert
Feuil13.Range("C5", "G" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil4.Range("A12")
Feuil13.Range("J5", "J" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil4.Range("I12")

Feuil4.Cells(12, 6) = "Quantity Available" & Chr(10) & "Quantité disponible"
Feuil4.Cells(12, 7) = "Unit Price" & Chr(10) & "Prix à l'unité"
Feuil4.Cells(12, 8) = "Total Price" & Chr(10) & "Prix Total"
Feuil4.Range("A12:E" & 11 + nb_selected_item).Copy
Feuil4.Range("F12:H" & 11 + nb_selected_item).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'insert CBA items
' corriger date

Feuil5.Rows(22 & ":" & 22 + nb_selected_item - 1).Insert
Feuil13.Range("C5", "G" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil5.Range("A22")
Feuil13.Range("L5", "W" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil5.Range("F22")

'insert PO items
'corriger date

Feuil7.Rows(31 & ":" & 31 + (nb_selected_item - 1)).Insert
Feuil13.Range("C5", "E" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("A31")
Feuil13.Range("X5", "X" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("D31")
Feuil13.Range("G5", "G" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("E31")
Feuil13.Range("Y5", "Y" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("G31")
Feuil13.Range("J5", "K" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("H31")
'get supplier selected
supplier = Feuil12.Cells(6, 2)
If (supplier = 1) Then
Feuil13.Range("M5", "M" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("F31")
Feuil6.Cells(11, 4) = Feuil2.Cells(r, 23)
Feuil6.Cells(13, 4) = Feuil2.Cells(r, 24)
Feuil6.Range("F15").Value = Feuil5.Cells(25 + (nb_selected_item + 2), 4).Value
ElseIf (supplier = 2) Then
Feuil13.Range("P5", "P" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("F31")
Feuil6.Cells(11, 4) = Feuil2.Cells(r, 31)
Feuil6.Cells(13, 4) = Feuil2.Cells(r, 32)
ElseIf (supplier = 3) Then
Feuil13.Range("S5", "S" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("F31")
Feuil6.Cells(11, 4) = Feuil2.Cells(r, 39)
Feuil6.Cells(13, 4) = Feuil2.Cells(r, 40)
ElseIf (supplier = 4) Then
Feuil13.Range("V5", "V" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil7.Range("F31")
Feuil6.Cells(11, 4) = Feuil2.Cells(r, 47)
Feuil6.Cells(13, 4) = Feuil2.Cells(r, 48)
End If


'insert GRN items
Feuil8.Rows(11 & ":" & 11 + (nb_selected_item - 1)).Insert
Feuil13.Range("C5", "G" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil8.Range("A11")
Feuil13.Range("Z5", "AA" & nb_item + 5).SpecialCells(xlVisible).Copy Destination:=Feuil8.Range("F11")

MsgBox ("All Your Forms are updated")


End Sub

'Select row filter items update parameters
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call my_purchase_followup
End Sub

Merci Beaucoup.

Le 24/02/2021 à 14:43

Bonjour,

Je ne suis pas certain d'avoir compris mais je propose une fonction directement qui renvoie le nombre de cellules visibles avec des sélections multiples :

function NBVISIBLES(Paramarray plage()) as long
for i = lbound(plage) to ubound(plage)
    NBVISIBLES = NBVISIBLES + Plage(i).SpecialCells(xlCellTypeVisible).Cells.Count
next i
end function

Pour poster du code, vous pouvez utiliser les balises </> du ruban d'icônes .

Cdlt,

Le 24/02/2021 à 14:53

Bonjour,

Une autre proposition :

Sheets("Feuil6").Range("F15").FormulaR1C1 = "=SUBTOTAL(109,Items!C[3])"
Le 24/02/2021 à 15:21

Bonjour, merci pour le retour.

Sheets("Feuil6").Range("F15").FormulaR1C1 = "=SUBTOTAL(109,Items!C[3])" il va sélectionner toutes les cellules non? moi je veux uniquement les visibles.

Merci 3GB pour le retour.

Cela compte les cellules ou fait la somme? Que veut dire plage(i)?

Ma cellule cible F15 recevrait le résultat de cette fonction?

Le 24/02/2021 à 15:23

Le code 109 de la fonction sous.total() permet d'additionner que les cellules visibles.

Le 24/02/2021 à 15:28

SOUS.TOTAL(109;Plage) renvoie la somme des cellules visibles (non filtrées). SOUS.TOTAL(103;Plage) en renvoie le nombre (nombre de valeurs, comprenant chaines vides, mais n'incluant pas les cellules vides).

La fonction compte le nombre de cellules visibles mais avec doublon cependant (pour l'instant). C'est-à-dire que si vous faites :

=NBVISIBLES(A1:A2;A2:A3)

Cela renverra 4 au lieu de 3 (le A2 étant répété).

paramarray plage() définit un tableau variable d'arguments à la fonction. Chaque argument est visé par son index (ordre dans lequel il est rentré en argument lors de la saisie de la fonction). Pour repartir sur l'exemple ci-dessus, on a A1:A2 qui représente la plage(0) et A2:A3 la plage(1).

En saisissant cette fonction en F15 ou en mettant dans le code :

range("F15").formula = "=NBVISIBLES(A1:A2, A3:A4)" 'par exemple
'ou bien
range("F15").value = NBVISIBLES(range("A1:A2"), range("A3:A4"))

vous obtiendriez le résultat en F15 en effet.

Le 24/02/2021 à 17:03

Merci pour la réponse.

J'ai mis cette formule: Feuil6.Range("F15").Value = NBVISIBLES(Range("I6:I2000"))

Le I2000 est juste pour lire toute les cellules sur la colonne I. sauf que la formule me renvoie 1995 ce qui est le nombre de cellules visibles.

Il me faut la somme des colonnes visibles sur la colonne I et non leur nombre à mettre dans Feuil6.range("F15")

D'ailleurs, comment faire d'ailleurs pour lire toute la colonne I sans définir un grand nombre?

Merci encore.

Le 24/02/2021 à 17:27

Décidément, je réponds à côté... Pour la somme, je vais repartir sur la proposition d'AntoineDL :

Feuil6.Range("F15").Value = evaluate("=SUBTOTAL(109, I:I)")

pour toute la colonne I. Ou alors :

Feuil6.Range("F15").Value = evaluate("=SUBTOTAL(109, I6:I" & rows.count & ")")

Mais peut-être qu'il serait préférable d'inscrire directement la formule sur la feuille...

Donc pour répondre à la question : rows.count permet d'obtenir le nombre de lignes de la feuille.

Cdlt,

Le 25/02/2021 à 09:54

Merci 3GB.

Au fait je préfère l'inclure dans le code VBA, car la colonne I n'est pas fixe. En gros, en fonction d'un autre paramètre j'additionnerai le contenu de la colonne H, ou J à la place. La formule serait trop longue..

La première formule que tu m'a proposé renvoie le résultat escompté. Mais elle n'est pas optimisée dans le sens ou elle va lire toute la colonne I, et mon fichier est lourd..

J'ai essayé d'utiliser le Row.count mais il me mets un #Value! sur mon F15. Feuil6.Range("F15").Value = evaluate("=SUBTOTAL(109, I6:I" & rows.count & ")") 

Le 25/02/2021 à 10:31

Bonjour LogCo,

D'accord, je croyais que tu cherchais à aller jusqu'en bas... Dans ce cas, il faut trouver la dernière ligne non vide :

dl = sheets("lafeuille").cells(rows.count, 9).end(xlup).row

Ca trouve la dernière cellule remplie en colonne I (la 9è) de la feuille "lafeuille".

Ensuite, la formule est à adapter ainsi :

Feuil6.Range("F15").Value = evaluate("=SUBTOTAL(109, I6:I" & dl & ")")

Cdlt,

Le 25/02/2021 à 12:27

Parfait It works!

Merci beaucoup 3GB pour ton support.

Rechercher des sujets similaires à "somme visibles vba"