erreur d execution

Français
#1

j ais le probleme suivant je n arrive pas a resoudre
Sub processing()
LR = ThisWorkbook.Worksheets(“Analyse”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 11 To LR
"“Erreur execution basic worksheet"423"”

#2

Bonjour @phw11348

L’extrait de code n’est pas assez complet pour se faire une idée mais de toute manière il faut inclure le mode de gestion du VBA. Voir l’aide ici et là.

[Ajout 17-02-21 16:10]

Je m’attendais davantage à un classeur contenant le module et des données pour tester…

À voir comme cela il y a tous les _ (caractères de continuation de ligne) qui ne doivent pas être suivis d’une ligne vide (mais c’est peut-être lié au collage dans l’éditeur de ce site).

Et puis les appels à

Application.WorksheetFunction.CountIfs(Range("E11:E" & DT3), sit, Range("F11:F" & DT3), V)

qui doivent être remplacés par :

oService.callFunction("COUNTIFS", array( oRange1, "Texte", oRange2, "String"))

Où oRange1 et oRange2 ont été créés par exemple comme ceci :

dim oService as object, oRange1 as object, oRange2 as object
oRange1 = thiscomponent.sheets.getByName("Analyse").getCellRangeByName("A1:A10")
oRange2 = thiscomponent.sheets.getByName("Analyse").getCellRangeByName("B1:B10")

Où oService est créé comme ceci :

oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")

Ici je compte les plages qui contiennent “Texte” et “String”, on pourrait bien sûr utiliser des variables comme dans ton code (sit et V)

Cordialement

#3

bonjour je vous passe le code qui ne marche pas

Rem Attribute VBA_ModuleType=VBAModule

Option VBASupport 1

Sub Add_New_line()

If ThisWorkbook.Worksheets(“Analyse”).Range(“A11”) = “” Then

MsgBox “La Ligne est déja vide”

Exit Sub

End If

ThisWorkbook.Worksheets(“Analyse”).Range(“A12”).EntireRow.Insert

ThisWorkbook.Worksheets(“Analyse”).Range(“A12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“A11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“B12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“B11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“C12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“C11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“D12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“D11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“E12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“E11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“F12”) = ThisWorkbook.Worksheets(“Analyse”).Range(“F11”)

ThisWorkbook.Worksheets(“Analyse”).Range(“A11”) = “”

ThisWorkbook.Worksheets(“Analyse”).Range(“B11”) = “”

ThisWorkbook.Worksheets(“Analyse”).Range(“C11”) = “”

ThisWorkbook.Worksheets(“Analyse”).Range(“D11”) = “”

ThisWorkbook.Worksheets(“Analyse”).Range(“E11”) = “”

ThisWorkbook.Worksheets(“Analyse”).Range(“F11”) = “”

MsgBox “Ligne ajoutée, Veuillez saisir les nouvelles données”

End Sub

Sub processing()

LR = ThisWorkbook.Worksheets(“Analyse”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 11 To LR

'Couple

If ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i) And _

ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) <> ThisWorkbook.Worksheets(“Analyse”).Range(“D” & i) Then

ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “Couple”

ThisWorkbook.Worksheets(“Analyse”).Range(“F” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i)

End If

If ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“D” & i) And _

ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i) <> ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) Then

ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “Couple”

ThisWorkbook.Worksheets(“Analyse”).Range(“F” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i)

End If

If ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“D” & i) And _

ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) <> ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i) Then

ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “Couple”

ThisWorkbook.Worksheets(“Analyse”).Range(“F” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i)

End If

'Trio

If ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“D” & i) And _

ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“C” & i) Then

ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “Trio”

ThisWorkbook.Worksheets(“Analyse”).Range(“F” & i) = ThisWorkbook.Worksheets(“Analyse”).Range(“B” & i)

End If

'Libre

If ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “” Then

ThisWorkbook.Worksheets(“Analyse”).Range(“E” & i) = “Libre”

ThisWorkbook.Worksheets(“Analyse”).Range(“F” & i) = “”

End If

Next i

MsgBox “Fin Processing -OK-”

Call Refresh

End Sub

Sub Refresh()

F3 = ThisWorkbook.Worksheets(“Analyse”).Range(“F3”)

F4 = ThisWorkbook.Worksheets(“Analyse”).Range(“F4”)

F5 = ThisWorkbook.Worksheets(“Analyse”).Range(“F5”)

ThisWorkbook.Worksheets(“Analyse”).Range(“F6”) = WorksheetFunction.CountA(Range(“A11:A1048576”))

LR = ThisWorkbook.Worksheets(“Analyse”).Range(“A” & Rows.Count).End(xlUp).Row

'Libre 10

ThisWorkbook.Worksheets(“Analyse”).Range(“G3”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F3), “Libre”)

'Libre 20

ThisWorkbook.Worksheets(“Analyse”).Range(“G4”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F4), “Libre”)

'Libre 30

ThisWorkbook.Worksheets(“Analyse”).Range(“G5”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F5), “Libre”)

'Libre All

ThisWorkbook.Worksheets(“Analyse”).Range(“G6”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & LR), “Libre”)

'Couple 10

ThisWorkbook.Worksheets(“Analyse”).Range(“H3”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F3), “Couple”)

'Couple 20

ThisWorkbook.Worksheets(“Analyse”).Range(“H4”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F4), “Couple”)

'Couple 30

ThisWorkbook.Worksheets(“Analyse”).Range(“H5”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F5), “Couple”)

'Couple All

ThisWorkbook.Worksheets(“Analyse”).Range(“H6”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & LR), “Couple”)

'Trio 10

ThisWorkbook.Worksheets(“Analyse”).Range(“I3”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F3), “Trio”)

'Trio 20

ThisWorkbook.Worksheets(“Analyse”).Range(“I4”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F4), “Trio”)

'Trio 30

ThisWorkbook.Worksheets(“Analyse”).Range(“I5”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F5), “Trio”)

'Trio All

ThisWorkbook.Worksheets(“Analyse”).Range(“I6”) = _

WorksheetFunction.CountIfs(Range(“E11:E” & LR), “Trio”)

'-----------------------//------------

'Numbre

'Libre 10

'For n = 11 To 20

'V = ThisWorkbook.Worksheets(“Analyse”).Cells(2, n)

'ThisWorkbook.Worksheets(“Analyse”).Cells(3, n).Value = _

'Application.WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F3), “Libre”, Range(“F11:F” & 10 + F3), V)

'Libre 20

'ThisWorkbook.Worksheets(“Analyse”).Cells(4, n).Value = _

'Application.WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F4), “Libre”, Range(“F11:F” & 10 + F4), V)

'Libre 30

'ThisWorkbook.Worksheets(“Analyse”).Cells(5, n).Value = _

'Application.WorksheetFunction.CountIfs(Range(“E11:E” & 10 + F5), “Libre”, Range(“F11:F” & 10 + F5), V)

'Libre All

'ThisWorkbook.Worksheets(“Analyse”).Cells(6, n).Value = _

'Application.WorksheetFunction.CountIfs(Range(“E11:E” & LR), “Libre”, Range(“F11:F” & LR), V)

'Next n

'-------------------------/OK/---------

Dim DT3, DT4, DT5, DT6 As Integer

DT3 = ThisWorkbook.Worksheets(“Analyse”).Range(“F3”) + 10

DT4 = ThisWorkbook.Worksheets(“Analyse”).Range(“F4”) + 10

DT5 = ThisWorkbook.Worksheets(“Analyse”).Range(“F5”) + 10

DT6 = ThisWorkbook.Worksheets(“Analyse”).Range(“F6”) + 10

’ Line 3

For n = 11 To 42

V = ThisWorkbook.Worksheets(“Analyse”).Cells(2, n)

If n > 10 And n < 21 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“K1”) '“Libre”

End If

If n > 21 And n < 32 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“V1”) '“couple”

End If

If n > 32 And n < 43 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“AG1”) '“Trio”

End If

ThisWorkbook.Worksheets(“Analyse”).Cells(3, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“E11:E” & DT3), sit, Range(“F11:F” & DT3), V)

If sit = “Libre” Then

ThisWorkbook.Worksheets(“Analyse”).Cells(3, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“B11:D” & DT3), V)

End If

Next n

’ Line 4

For n = 11 To 42

V = ThisWorkbook.Worksheets(“Analyse”).Cells(2, n)

If n > 10 And n < 21 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“K1”) '“Libre”

End If

If n > 21 And n < 32 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“V1”) '“couple”

End If

If n > 32 And n < 43 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“AG1”) '“Trio”

End If

ThisWorkbook.Worksheets(“Analyse”).Cells(4, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“E11:E” & DT4), sit, Range(“F11:F” & DT4), V)

If sit = “Libre” Then

ThisWorkbook.Worksheets(“Analyse”).Cells(4, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“B11:D” & DT4), V)

End If

Next n

’ Line 5

For n = 11 To 42

V = ThisWorkbook.Worksheets(“Analyse”).Cells(2, n)

If n > 10 And n < 21 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“K1”) '“Libre”

End If

If n > 21 And n < 32 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“V1”) '“couple”

End If

If n > 32 And n < 43 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“AG1”) '“Trio”

End If

ThisWorkbook.Worksheets(“Analyse”).Cells(5, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“E11:E” & DT5), sit, Range(“F11:F” & DT5), V)

If sit = “Libre” Then

ThisWorkbook.Worksheets(“Analyse”).Cells(5, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“B11:D” & DT5), V)

End If

Next n

’ Line 6

For n = 11 To 42

V = ThisWorkbook.Worksheets(“Analyse”).Cells(2, n)

If n > 10 And n < 21 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“K1”) '“Libre”

End If

If n > 21 And n < 32 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“V1”) '“couple”

End If

If n > 32 And n < 43 Then

sit = ThisWorkbook.Worksheets(“Analyse”).Range(“AG1”) '“Trio”

End If

ThisWorkbook.Worksheets(“Analyse”).Cells(6, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“E11:E” & DT6), sit, Range(“F11:F” & DT6), V)

If sit = “Libre” Then

ThisWorkbook.Worksheets(“Analyse”).Cells(6, n).Value = _

Application.WorksheetFunction.CountIfs(Range(“B11:D” & DT6), V)

End If

Next n

'-------------------------//-------------

MsgBox “Fin Refresh -Ok-”

End Sub

Provenance : Courrier pour Windows 10

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.