Macro d'un classeur affichant une erreur dans PERSONAL.XLSB
Bonjour à tous & à toutes,
Je réalise en ce moment un classeur pour différents collègues et viens de découvrir le bénéfice de mettre ses macros dans le fichier PERSONAL.XLSB
J'ai ainsi transféré toutes mes macros dans ce fichier afin qu'elles soient accessibles plus facilement.
Cependant, lors du transfert de ma macro SeparerNomPrenom(), j'ai eu une erreur que je ne suis pas capable de résoudre :
"nombre d'arguments incorrect ou affectation de propriété incorrecte"
Voici ma macro
Sub SeparerNomPrenom()
Dim firstSpace As Long
Dim strName As String
Dim firstName As String
Dim lastName As String
Set plage = Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each cell In plage
strName = Trim(cell) ' <-- l'erreur apparaît à cet endroit
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Trim(Left(strName, firstSpace))
lastName = Trim(Right(strName, Len(strName) - firstSpace))
cell.Value = firstName
cell.Offset(0, 1) = lastName
End If
Next
End Sub
D'avance merci,
FirstName
Slt Firstname,
essaie comme ca
Sub SeparerNomPrenom()
Dim firstSpace As Long
Dim strName As String
Dim firstName As String
Dim lastName As String
Dim plage As Range, cell As Range
Set plage = Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each cell In plage
strName = Trim(cell) ' <-- l'erreur apparaît à cet endroit
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Trim(Left(strName, firstSpace))
lastName = Trim(Right(strName, Len(strName) - firstSpace))
cell.Value = firstName
cell.Offset(0, 1) = lastName
End If
Next
End Sub
Salut m3ellem1 et merci pour ta réponse.
Cela ne fonctionne pas mieux.
Bonjour,
Essaie ainsi :
Public Sub SeparerNomPrenom()
Dim strName As String
Dim vText As Variant
Dim Cell As Range, Plage As Range
Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each Cell In Plage
If Not IsEmpty(Cell) Then
strName = Application.Trim(Cell.Value)
If InStr(1, strName, " ", vbTextCompare) Then
vText = Split(strName)
Cell.Value = vText(0)
Cell.Offset(0, 1).Value = vText(1)
End If
End If
Next Cell
End Sub
et comme ca?
Sub SeparerNomPrenom()
Dim firstSpace As Long
Dim strName As String
Dim firstName As String
Dim lastName As String
Dim plage As Range, cell As Range
Set plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each cell In plage
strName = Trim(cell) ' <-- l'erreur apparaît à cet endroit
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Trim(Left(strName, firstSpace))
lastName = Trim(Right(strName, Len(strName) - firstSpace))
cell.Value = firstName
cell.Offset(0, 1) = lastName
End If
Next
End Sub
Re,
Modifie ainsi, mais ta procédure doit envisager tous les cas possibles :
Public Sub SeparerNomPrenom()
Dim strName As String
Dim vText As Variant
Dim Cell As Range, Plage As Range
Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each Cell In Plage
If Not IsEmpty(Cell) Then
strName = Application.Trim(Cell.Value)
If InStr(1, strName, " ", vbTextCompare) Then
vText = Split(strName)
Cell.Value = vText(0)
Cell.Offset(0, 1).Value = vText(1)
Else
Cell.Offset(0, 1).Value = Cell.Value
End If
End If
Next Cell
End Sub
et comme ca?
Sub SeparerNomPrenom() Dim firstSpace As Long Dim strName As String Dim firstName As String Dim lastName As String Dim plage As Range, cell As Range Set plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row) For Each cell In plage strName = Trim(cell) ' <-- l'erreur apparaît à cet endroit If InStr(1, strName, " ", vbTextCompare) Then firstSpace = InStr(1, strName, " ", vbTextCompare) firstName = Trim(Left(strName, firstSpace)) lastName = Trim(Right(strName, Len(strName) - firstSpace)) cell.Value = firstName cell.Offset(0, 1) = lastName End If Next End Sub
Toujours la même erreur
Re,
Modifie ainsi, mais ta procédure doit envisager tous les cas possibles :
Public Sub SeparerNomPrenom() Dim strName As String Dim vText As Variant Dim Cell As Range, Plage As Range Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row) For Each Cell In Plage If Not IsEmpty(Cell) Then strName = Application.Trim(Cell.Value) If InStr(1, strName, " ", vbTextCompare) Then vText = Split(strName) Cell.Value = vText(0) Cell.Offset(0, 1).Value = vText(1) Else Cell.Offset(0, 1).Value = Cell.Value End If End If Next Cell End Sub
En fait, j'aimerais que cela fonctionne de cette manière :
Re,
Public Sub SeparerNomPrenom()
Dim strName As String
Dim vText As Variant
Dim Cell As Range, Plage As Range
Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each Cell In Plage
If Not IsEmpty(Cell) Then
strName = Application.Trim(Cell.Value)
If InStr(1, strName, " ", vbTextCompare) Then
vText = Split(strName)
Cell.Value = vText(0)
Cell.Offset(0, 1).Value = vText(1)
Else
Cell.Offset(0, 1).Value = vbNullString
End If
End If
Next Cell
End Sub
Re,
Public Sub SeparerNomPrenom() Dim strName As String Dim vText As Variant Dim Cell As Range, Plage As Range Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row) For Each Cell In Plage If Not IsEmpty(Cell) Then strName = Application.Trim(Cell.Value) If InStr(1, strName, " ", vbTextCompare) Then vText = Split(strName) Cell.Value = vText(0) Cell.Offset(0, 1).Value = vText(1) Else Cell.Offset(0, 1).Value = vbNullString End If End If Next Cell End Sub
Le problème avec "Cell.Offset(0, 1).Value = vText(1)" c'est que cela ne prend pas vText(2) et plus ...
Slt,
dernier essai, chez moi ca fonctionne!
Sub SeparerNomPrenom()
Dim strName As String
Dim firstSpace As Long
Dim firstName As String
Dim lastName As String
Dim Cell As Range, Plage As Range
Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each Cell In Plage
If Not IsEmpty(Cell) Then
strName = Application.Trim(Cell.Value)
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Trim(Left(strName, firstSpace))
lastName = Trim(Right(strName, Len(strName) - firstSpace))
Cell.Value = firstName
Cell.Offset(0, 1) = lastName
End If
End If
Next Cell
End Sub
Slt,
dernier essai, chez moi ca fonctionne!
Sub SeparerNomPrenom() Dim strName As String Dim firstSpace As Long Dim firstName As String Dim lastName As String Dim Cell As Range, Plage As Range Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row) For Each Cell In Plage If Not IsEmpty(Cell) Then strName = Application.Trim(Cell.Value) If InStr(1, strName, " ", vbTextCompare) Then firstSpace = InStr(1, strName, " ", vbTextCompare) firstName = Trim(Left(strName, firstSpace)) '<-- Ici au niveau du Trim lastName = Trim(Right(strName, Len(strName) - firstSpace)) Cell.Value = firstName Cell.Offset(0, 1) = lastName End If End If Next Cell End Sub
J'ai toujours et encore la même erreur (que je ne comprends pas d'ailleurs)
LOL vraiment le dernier essai
Sub SeparerNomPrenom()
Dim strName As String
Dim firstSpace As Long
Dim firstName As String
Dim lastName As String
Dim Cell As Range, Plage As Range
Set Plage = ActiveSheet.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each Cell In Plage
If Not IsEmpty(Cell) Then
strName = Application.Trim(Cell.Value)
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Application.Trim(Left(strName, firstSpace))
lastName = Application.Trim(Right(strName, Len(strName) - firstSpace))
Cell.Value = firstName
Cell.Offset(0, 1) = lastName
End If
End If
Next Cell
End Sub
Pfiou eh bien merci beaucoup !
Du coup à quoi était due cette erreur ?
Pfiou eh bien merci beaucoup !
Du coup à quoi était due cette erreur ?
Application. avant le Trim
en plus ton premier code devrait fonctionner aussi en ajoutant Application. à tester
Sub SeparerNomPrenom()
Dim firstSpace As Long
Dim strName As String
Dim firstName As String
Dim lastName As String
Set plage = Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
For Each cell In plage
strName = Application.Trim(cell) ' <-- l'erreur apparaît à cet endroit
If InStr(1, strName, " ", vbTextCompare) Then
firstSpace = InStr(1, strName, " ", vbTextCompare)
firstName = Application.Trim(Left(strName, firstSpace))
lastName = Application.Trim(Right(strName, Len(strName) - firstSpace))
cell.Value = firstName
cell.Offset(0, 1) = lastName
End If
Next
End Sub