COUNTA avec VBA, répétitif sur plusieurs colonnes

Bonjour,

(je pensais avoir publié ce poste hier mais je ne le retrouve pas bizaremment donc je le reposte à nouveau)

J'ai un tableau avec dans chaque colonne (à partir de A) une date à la ligne 6 et des noms à partir de la ligne 7

Je cherche une fonction qui compte avec la fonction COUNTA le nombre de ligne de la même colonne à partir de la ligne 7 qui ont une valeur (un nom) et qui inscrive le résultat dans la cellule A4

Pareil pour chaque colonne, et inscription dans B4, C4 etc

Je dois absolument utiliser VBA et non une formule dans la cellule car toutes les cellules de l'onglet peuvent s'effacer via une autre macro et dans ce cas les formules aussi le sont. C'est pourquoi VBA est ma seule solution...

J'ai essayé de bricoler la macro suivante mais bien sûr elle bug

Private Sub Worksheet_Change(ByVal Target As Range)

Dim numwords As Integer

Dim i As Long

With ActiveSheet

For i = 1 To Rows.Count

numwords = WorksheetFunction.CountA(Range(Cells(7, i)), Cells(200, i))

Cells(4, i) = numwords & " " & "used rooms"

Next i

End With

End Sub

Bonjour,

Effectivement, il y a pas mal d'erreurs :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim numwords As Integer
Dim i As Long

With ActiveSheet 
For i = 1 To Rows.Count
numwords = WorksheetFunction.CountA(Range(Cells(7, i)), Cells(200, i))

Cells(4, i) = numwords & " " & "used rooms"

Next i
End With
End Sub

Dans l'ordre :

1. Ta macro démarre à chaque changement opéré sur la feuille dans laquelle se trouve le code, sauf qu'elle vient elle-même y apporter des modifications et risque donc de s'appeler elle-même un paquet de fois ! Pourquoi ne pas faire une macro qui s’exécute à partir d'un bouton ?

2. Ta macro fonctionne à partir d'un événement (ici un changement) associé à une feuille définie (celle associée au module dans lequel se trouve ton code). Donc il est inutile, voire contre-productif de lui dire que tu vas travailler sur la feuille active avec une instruction "With ActiveSheet".

3. Row = Ligne, or tu cherches à faire varier l'indice de colonne (column). Aussi, Rows.Count te donne le n° de la dernière ligne possible du classeur, qu'elle soit complétée ou non (donc ligne 1048576 depuis Excel 2007). Sachant qu'il n'y a que 16 384 colonnes au maximum, ça fera forcément planter ta macro si tu utilises ce nombre comme indice de colonne. Je t'invite à consulter ce lien pour définir ta plage.

Bonjour à tous,

j'ajouterais qu'il y a une parenthèse mal placée dans l'instruction

numwords = WorksheetFunction.CountA(Range(Cells(7, i)), Cells(200, i))

devrait être

numwords = WorksheetFunction.CountA(Range(Cells(7, i), Cells(200, i)))

une proposition d'adaptation correspondant à ta demande ...

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim numwords As Long
    Dim i As Long, ncol As Long

    Application.EnableEvents = False

        ncol = UsedRange.Columns.Count
        For i = 1 To ncol
            numwords = WorksheetFunction.CountA(Range(Cells(7, i), Cells(200, i)))
            Cells(4, i) = numwords & " " & "used rooms"
        Next i
        Application.EnableEvents = True
End Sub

Merci beaucoup à vous deux!!

Est-ce possible de rajouter une condition pour l'activation de la macro selon laquelle , si la cellule de la ligne 6 est vide alors la macro s'arrête (ceci afin d'éviter qu'elle n'inscrive le résultat dans le cellule de la ligne 4 lorsque la ligne 6 est vide dans la même colonne...) ?

en gras c'est l'idee pour la condition...

Sub Worksheet_Change(ByVal Target As Range)

Dim numwords As Long

Dim i As Long, ncol As Long

Application.EnableEvents = False

If (Cells(6, i)) = 0 Then Exit Sub

End If

ncol = UsedRange.Columns.Count

For i = 1 To ncol

numwords = WorksheetFunction.CountA(Range(Cells(7, i), Cells(200, i)))

Cells(4, i) = numwords & " " & "used rooms"

Next i

Application.EnableEvents = True

End Sub

Bonjour,

non testé

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim numwords As Long
    Dim i As Long, ncol As Long

    Application.EnableEvents = False
    ncol = UsedRange.Columns.Count
    For i = 1 To ncol
        If Cells(i, 6) <> "" Then
            numwords = WorksheetFunction.CountA(Range(Cells(7, i), Cells(200, i)))
            Cells(4, i) = numwords & " " & "used rooms"
        Next i
    End If
    Application.EnableEvents = True

End Sub

cela provoque un bug: "Compile error: next without for"

ericw a écrit :

cela provoque un bug: "Compile error: next without for"

Une petite erreur d'inattention je pense, dans le code, il faut inverser ces 2 instructions :

'...
        Next i
    End If
'...

Comme ceci :

'...
        End If
    Next i
'...

merci Pedro

h2so4 a écrit :

merci Pedro

J'en fais tellement que ça me fait plaisir de ne pas être le seul !

Merci Pedro et h2so4

J'ai un peu arrangé le code et il marche!

le voici:

MERCI

Private Sub Worksheet_Change(ByVal Target As Range)

Dim numwords As Long

Dim i As Long, ncol As Long

Application.EnableEvents = False

ncol = UsedRange.Columns.Count

For i = 1 To ncol

If Cells(6, i) = 0 Then

Cells(4, i) = 0

Else

numwords = WorksheetFunction.counta(Range(Cells(7, i), Cells(200, i)))

If numwords = 0 Then

Cells(4, i) = " "

Else

Cells(4, i) = numwords & " " & "used rooms"

End If

End If

Next i

Application.EnableEvents = True

End Sub