Sub test_chr() Dim TexteCell As String nblignes = 10 ; Nb lignes max à traiter. For a = 1 To nblignes TexteCell = Range("B" & a).Value ' recuperation du texte nbChr = CountOccu(TexteCell, Chr(10)) Debug.Print nbChr Next End Sub Public Function CountOccu(ByRef vsInput As String, ByRef vsPattern As String, Optional ByVal veCompare As VbCompareMethod = vbBinaryCompare) As Integer Dim i As Long i = InStr(1, vsInput, vsPattern, veCompare) Do While i CountOccu = CountOccu + 1 i = InStr(i + 1, vsInput, vsPattern, veCompare) Loop End Function
Sub Redim_chr() Dim TexteCell As String nblignes = 10 ' Nb lignes max à traiter. For a = 1 To nblignes TexteCell = Range("B" & a).Value ' recuperation du texte 'Nb de retours chariots dans la cellule : nbChr = CountOccu(TexteCell, Chr(10)) Debug.Print nbChr 'Taille à mettre sur la ligne : Nouvelle_Taille = nbChr * 12.75 + 12.75 Debug.Print Nouvelle_Taille 'Application de la taille dans la ligne (a) : ActiveSheet.Rows(a).RowHeight = Nouvelle_Taille Next End Sub Public Function CountOccu(ByRef vsInput As String, ByRef vsPattern As String, Optional ByVal veCompare As VbCompareMethod = vbBinaryCompare) As Integer Dim i As Long i = InStr(1, vsInput, vsPattern, veCompare) Do While i CountOccu = CountOccu + 1 i = InStr(i + 1, vsInput, vsPattern, veCompare) Loop End Function
Application.ScreenUpdating = False
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionPrivate Sub CommandButton9_Click() Dim nbrlignes Dim a Dim nbChr Dim TexteCellule As String Dim lastcell Dim tailleligne With ThisWorkbook.ActiveSheet lastcell = ActiveSheet.Range("B1000").End(xlUp).Row nbrlignes = lastcell ' Nb lignes max à traiter. For a = 1 To nbrlignes TexteCellule = Range("B" & a).Value ' recuperation du texte nbChr = CountOccu(TexteCellule, Chr(10)) tailleligne = 12.75 + 12.75 * (nbChr) Range("B" & a).RowHeight = tailleligne Debug.Print nbChr Next a End With End Sub Public Function CountOccu(ByRef vsInput As String, ByRef vsPattern As String, Optional ByVal veCompare As VbCompareMethod = vbBinaryCompare) As Integer Dim i As Long i = InStr(1, vsInput, vsPattern, veCompare) Do While i CountOccu = CountOccu + 1 i = InStr(i + 1, vsInput, vsPattern, veCompare) Loop End Function