XL 2013 trop de caractère de continuité de ligne

[tactic6]

Bonjour à tous
Je n'ai pas trouvé sur le forum la réponse à mon problème, un pro d'excel peut il me venir en aide ?
j'ai macro qui copie des cellules d'une feuille pour les réécrire à l'horizontale vers une autre.
Aujourd'hui je dois augmenter le nombre de cellules copiées et recopiées mais apparemment il y en a trop

A partir de la ligne signalée j'ai le message "trop de caractère de continuité de ligne" et impossible de finir la ligne de code avec & _
Il y a t'il une autre solution ou astuce pour forcer excel ?

Sub Transfert2()
Dim Ligne As Long
Dim colonne As Long
Dim Cellule As Range
Dim Lg
Dim Derli As Long
Dim i As Long, sStr As String, Ar() As String
'Application.ScreenUpdating = False
    Sheets("SAISIE").Select
    Sheets("ShArchive").Visible = True

    sStr = "I6,I5,C12,G8,H9,G10,h12,B15,C15,H15,I15,J15,K15,B16,C16,H16,I16,J16,K16," & _
           "B17,C17,H17,I17,J17,K17,B18,C18,H18,I18,J18,K18,B19,C19,H19,I19,J19,K19," & _
           "B20,C20,H20,I20,J20,K20,B21,C21,H21,I21,J21,K21,B22,C22,H22,I22,J22,K22," & _
           "B23,C23,H23,I23,J23,K23,B24,C24,H24,I24,J24,K24,B25,C25,H25,I25,J25,K25," & _
           "B26,C26,H26,I26,J26,K26,B27,C27,H27,I27,J27,K27,B28,C28,H28,I28,J28,K28," & _
           "B29,C29,H29,I29,J29,K29,B30,C30,H30,I30,J30,K30,B31,C31,H31,I31,J31,K31," & _
           "B32,C32,H32,I32,J32,K32,B33,C33,H33,I33,J33,K33,B34,C34,H34,I34,J34,K34," & _
           "B35,C35,H35,I35,J35,K35,B36,C36,H36,I36,J36,K36,B37,C37,H37,I37,J37,K37," & _
           "B38,C38,H38,I38,J38,K38,B39,C39,H39,I39,J39,K39,B40,C40,H40,I40,J40,K40," & _
           "B41,C41,H41,I41,J41,K41,B42,C42,H42,I42,J42,K42,B43,C43,H43,I43,J43,K43," & _
           "B44,C44,H44,I44,J44,K44,B45,C45,H45,I45,J45,K45,B46,C46,H46,I46,J46,K46," & _
           "B47,C47,H47,I47,J47,K47,B48,C48,H48,I48,J48,K48,B49,C49,H49,I49,J49,K49," & _
           "B50,C50,H50,I50,J50,K50,B51,C51,H51,I51,J51,K51,B52,C52,H52,I52,J52,K52," & _
           "B53,C53,H53,I53,J53,K53,B54,C54,H54,I54,J54,K54,B55,C55,H55,I55,J55,K55," & _
           "B56,C56,H56,I56,J56,K56,B57,C57,H57,I57,J57,K57,B58,C58,H58,I58,J58,K58," & _
           "B59,C59,H59,I59,J59,K59,B85,C85,H85,I85,J85,K85,B86,C86,H86,I86,J86,K86," & _
           "B87,C87,H87,I87,J87,K87,B88,C88,H88,I88,J88,K88,B89,C89,H89,I89,J89,K89," & _
           "B90,C90,H90,I90,J90,K90,B91,C91,H91,I91,J91,K91,B92,C92,H92,I92,J92,K92," & _
           "B93,C93,H93,I93,J93,K93,B94,C94,H94,I94,J94,K94,B95,C95,H95,I95,J95,K95," & _
           "B96,C96,H96,I96,J96,K96,B97,C97,H97,I97,J97,K97,B98,C98,H98,I98,J98,K98," & _
           "B99,C99,H99,I99,J99,K99,B100,C100,H100,I100,J100,K100,B101,C101,H101,I101,J101,K101," & _
           "B102,C102,H102,I102,J102,K102,B103,C103,H103,I103,J103,K103,B104,C104,H104,I104,J104,K104," & _
           "B105,C105,H105,I105,J105,K105,B106,C106,H106,I106,J106,K106,B107,C107,H107,I107,J107,K107," & _
           "B108,C108,H108,I108,J108,K108,B109,C109,H109,I109,J109,K109,B110,C110,H110,I110,J110,K110," & _
           "B111,C111,H111,I111,J111,K111,B112,C112,H112,I112,J112,K112,B113,C113,H113,I113,J113,K113," &       erreur ici impossible de continuer
           "B114,C114,H114,I114,J114,K114,B115,C115,H115,I115,J115,K115,B116,C116,H116,I116,J116,K116," & _
           "B117,C117,H117,I117,J117,K117,B118,C118,H118,I118,J118,K118,B119,C119,H119,I119,J119,K119," & _
           "B120,C120,H120,I120,J120,K120,B121,C121,H121,I121,J121,K121,B122,C122,H122,I122,J122,K122," & _
           "C125,C126,C127,D125,D126,D127,F128,F129,J125,J126,J127,J128,j129"

    Ar = Split(sStr, ",")

    Ligne = Worksheets("ShArchive").Range("A" & Rows.Count).End(xlUp).Row + 1

    Lg = Application.Match(Range(Ar(0)), Sheets("ShArchive").Range("A1:A" & Ligne), 0)

    ' Si erreur : Pas de doublon
    ' sinon Lg contient la ligne en double
    If Not IsError(Lg) Then

 
        Ligne = Lg
 
    End If

    ' Remplit les colonnes
    For i = LBound(Ar) To UBound(Ar)
        colonne = colonne + 1
        Worksheets("ShArchive").CellS(Ligne, colonne) = Worksheets("SAISIE").Range(Ar(i))
    Next i
    'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

merci

 

[Hasco]

Bonsoir,

Solution simple: faire des lignes de texte plus longues pour avoir moins de trait de continuation '_'. :

sStr = "I6,I5,C12,G8,H9,G10,h12,B15,C15,H15,I15,J15,K15,B16,C16,H16,I16,J16,K16,B17,C17,H17,I17,J17,K17,B18,C18,H18,I18,J18,K18,B19,C19,H19,I19,J19,K19," & _
           "B20,C20,H20,I20,J20,K20,B21,C21,H21,I21,J21,K21,B22,C22,H22,I22,J22,K22,B23,C23,H23,I23,J23,K23,B24,C24,H24,I24,J24,K24,B25,C25,H25,I25,J25,K25," & _
           "B26,C26,H26,I26,J26,K26,B27,C27,H27,I27,J27,K27,B28,C28,H28,I28,J28,K28,B29,C29,H29,I29,J29,K29,B30,C30,H30,I30,J30,K30,B31,C31,H31,I31,J31,K31," & _
           "B32,C32,H32,I32,J32,K32,B33,C33,H33,I33,J33,K33,B34,C34,H34,I34,J34,K34,B35,C35,H35,I35,J35,K35,B36,C36,H36,I36,J36,K36,B37,C37,H37,I37,J37,K37," & _
           "B38,C38,H38,I38,J38,K38,B39,C39,H39,I39,J39,K39,B40,C40,H40,I40,J40,K40,B41,C41,H41,I41,J41,K41,B42,C42,H42,I42,J42,K42,B43,C43,H43,I43,J43,K43," & _
           "B44,C44,H44,I44,J44,K44,B45,C45,H45,I45,J45,K45,B46,C46,H46,I46,J46,K46,B47,C47,H47,I47,J47,K47,B48,C48,H48,I48,J48,K48,B49,C49,H49,I49,J49,K49," & _
           "B50,C50,H50,I50,J50,K50,B51,C51,H51,I51,J51,K51,B52,C52,H52,I52,J52,K52," & _
           "B53,C53,H53,I53,J53,K53,B54,C54,H54,I54,J54,K54,B55,C55,H55,I55,J55,K55," & _
           "B56,C56,H56,I56,J56,K56,B57,C57,H57,I57,J57,K57,B58,C58,H58,I58,J58,K58," & _
           "B59,C59,H59,I59,J59,K59,B85,C85,H85,I85,J85,K85,B86,C86,H86,I86,J86,K86," & _
           "B87,C87,H87,I87,J87,K87,B88,C88,H88,I88,J88,K88,B89,C89,H89,I89,J89,K89," & _
           "B90,C90,H90,I90,J90,K90,B91,C91,H91,I91,J91,K91,B92,C92,H92,I92,J92,K92," & _
           "B93,C93,H93,I93,J93,K93,B94,C94,H94,I94,J94,K94,B95,C95,H95,I95,J95,K95," & _
           "B96,C96,H96,I96,J96,K96,B97,C97,H97,I97,J97,K97,B98,C98,H98,I98,J98,K98," & _
           "B99,C99,H99,I99,J99,K99,B100,C100,H100,I100,J100,K100,B101,C101,H101,I101,J101,K101," & _
           "B102,C102,H102,I102,J102,K102,B103,C103,H103,I103,J103,K103,B104,C104,H104,I104,J104,K104," & _
           "B105,C105,H105,I105,J105,K105,B106,C106,H106,I106,J106,K106,B107,C107,H107,I107,J107,K107," & _
           "B108,C108,H108,I108,J108,K108,B109,C109,H109,I109,J109,K109,B110,C110,H110,I110,J110,K110," & _
           "B111,C111,H111,I111,J111,K111,B112,C112,H112,I112,J112,K112,B113,C113,H113,I113,J113,K113," & _
           "B114,C114,H114,I114,J114,K114,B115,C115,H115,I115,J115,K115,B116,C116,H116,I116,J116,K116," & _
           "B117,C117,H117,I117,J117,K117,B118,C118,H118,I118,J118,K118,B119,C119,H119,I119,J119,K119," & _
           "B120,C120,H120,I120,J120,K120,B121,C121,H121,I121,J121,K121,B122,C122,H122,I122,J122,K122," & _
           "C125,C126,C127,D125,D126,D127,F128,F129,J125,J126,J127,J128,j129"

Bon courage

 

[tactic6]

Merci j'avais augmenté mais pas assez
Tu me sauves
Merci

 

[eriiic]

Bonjour,

puisqu'à terme tu t'en sers de Range, pourquoi ne pas les déclarer directement sous cette forme ?
Ex :

    ...
    Dim pl As Range, c As Range
    Set pl = Range("I6,I5,C12,G8,H9,G10,h12,B15,C15,H15,I15,J15,K15,B16,C16,H16,I16,J16,K16")
    Set pl = Union(pl, Range("B17,C17,H17,I17,J17,K17,B18,C18,H18,I18,J18,K18,B19,C19,H19,I19,J19,K19"))
    Set pl = Union(pl, Range("B20,C20,H20,I20,J20,K20,B21,C21,H21,I21,J21,K21,B22,C22,H22,I22,J22,K22"))
    ' etc

    Ligne = Worksheets("ShArchive").Range("A" & Rows.Count).End(xlUp).Row + 1
    For Each c In pl
        Lg = Application.Match(c.Value, Sheets("ShArchive").Range("A1:A" & Ligne), 0)
    Next c
    ' ...

Et encore plus simple : tu sélectionnes toutes tes cellules et tu nommes la plage.
eric

 

[tactic6]

oui aussi
Merci pour l'astuce