Multiple Lines of code error RRS feed

  • Question

  • I have single line of code for cell references to a worksheet that I'm trying create multiply lines to make it easier to read. I'm not sure if I have to many references or I've split them up incorrectly. When I run the macro I get an error.


    Sub UpdateLogWorksheet()
        Dim ECNWks As Worksheet
        Dim DataLogWks As Worksheet
        Dim nextRow As Long
        Dim oCol As Long

        Dim myRng As Range
        Dim myCopy As String
        Dim myCell As Range
        'cells to copy from ECN sheet - some contain formulas
        myCopy = "AI1,Q6,AI6,Q8,AI8,G10,AC10,G12,AC12,AK12,K14,K16,A20,A26" _
            & "O34,U34,Y34,AC34,O36,U36,Y36,AC36,O38,U38,Y38,AC38" _
            & "A46,I46,M46,Q46,S46,AE46,AI46,AM46,A48,I48,M48,Q48,S48,AE48,AI48,AM48,A50,I50,M50,Q50,S50,AE50,AI50,AM50,A52,I52,M52,Q52,S52,AE52,AI52,AM52" _
            & "E56,W56,AA56,AI56,E58,W58,AA58,AI58,E60,W60,AA60,AI60,E62,W62,AA62,AI62,O65,S65,W65,AA65,AI65,K67,K69,K71,AI71"
        Set ECNWks = Worksheets("ECN")
        Set DataLogWks = Worksheets("DataLogECN")

        With DataLogWks
            nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        End With

        With ECNWks
            Set myRng = .Range(myCopy)

        With DataLogWks
            With .Cells(nextRow, "A")
                .Value = Now
                .NumberFormat = "mm/dd/yyyy hh:mm:ss"
            End With
            .Cells(nextRow, "B").Value = Application.UserName
            oCol = 3
            For Each myCell In myRng.Cells
                DataLogWks.Cells(nextRow, oCol).Value = myCell.Value
                oCol = oCol + 1
            Next myCell
        End With
        'clear ECN cells that contain constants
        With ECNWks
          On Error Resume Next
             With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
                  Application.GoTo .Cells(1) ', Scroll:=True
             End With
          On Error GoTo 0
        End With
        End With
    End Sub


    Thursday, May 2, 2013 3:13 PM

All replies

  • You are limited to 255 characters when you pass an address string to a range object, so your address is too long, and you made a mistake when you broke up the string - you left off commas between the concatenated strings. Anyway, you can do this:

    Dim rngC As Range
    Dim myCopy1 As String
    Dim myCopy2 As String
    myCopy1 = "AI1,Q6,AI6,Q8,AI8,G10,AC10,G12,AC12,AK12,K14,K16,A20,A26," _
              & "O34,U34,Y34,AC34,O36,U36,Y36,AC36,O38,U38,Y38,AC38," _
              & "A46,I46,M46,Q46,S46,AE46,AI46,AM46,A48,I48,M48,Q48,S48"
    myCopy2 = "AE48,AI48,AM48,A50,I50,M50,Q50,S50,AE50,AI50,AM50,A52,I52,M52,Q52," _
              & "S52,AE52,AI52,AM52,E56,W56,AA56,AI56,E58,W58,AA58,AI58,E60,W60,AA60," _
              & "AI60,E62,W62,AA62,AI62,O65,S65,W65,AA65,AI65,K67,K69,K71,AI71"
    Set rngC = Union(Range(myCopy1), Range(myCopy2))

    and then use rngC instead of Range(myCopy) later in your code

    Thursday, May 2, 2013 3:31 PM