Announcement

Collapse
No announcement yet.

Concatenate Each Row To Cells In Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Concatenate Each Row To Cells In Column

    Hi all,

    I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set

    I believe the code below is close, but I am receiving this error
    Run-time error '91':
    Object variable or with block variable not set
    Debug points to this line
    Code:
                rngComment = .Cells(intCounter, 5)
    I'm not sure what I am doing wrong.
    Can you please nudge me in the right direction?
    Many thanks!
    Regards,
    marc

    Code:
    Option Explicit
    
    Sub wo_Consolidate_Comments()
        Dim wbBook As Workbook
        Dim wsData As Worksheet
        Dim strComment As String
        Dim rngComment As Range
        Dim lngrows As Long
        Dim intCounter As Integer
        Dim intRecord As Integer
        
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.DisplayAlerts = False
        
        Set wbBook = ThisWorkbook
        Set wsData = wbBook.Worksheets("Data")
        lngrows = wsData.Range("A65536").End(xlUp).Row
        intCounter = 2
        intRecord = intCounter
        strComment = wsData.Cells(intCounter, 5)
    
        With wsData
            Do While lngrows >= 2
                rngComment = .Cells(intCounter, 5)
                
                If .Cells(intCounter, 1) = .Cells(intCounter + 1, 1) Then
                    strComment = strComment + .Cells(intCounter + 1, 5)
                Else
                    rngComment = strComment
                    intRecord = intCounter
                End If
                
                intCounter = intCounter + 1
                lngrows = lngrows - 1
            Loop
        End With
                
            
        
        '//Cleanup
        Set wbBook = Nothing
        Set wsData = Nothing
        Set rngComment = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
         
    End Sub

  • #2
    Re: Concatenate Variable Number Of Rows To One Comment For Each Record

    What's the value of intCounter at the time of error?
    What value is in the cell?

    Comment


    • #3
      Re: Concatenate Each Row To Cells In Column

      Thanks Dave,

      Code:
      intCounter = 2
      .Cells(intCounter,5) = "71 - Vending Services"
      Thanks!
      Regards,
      marc
      Last edited by markc; August 8th, 2008, 21:04.

      Comment


      • #4
        Re: Concatenate Each Row To Cells In Column

        Move the With Statement & End With inside the loop and edit edit you post above to include code tags.

        Comment


        • #5
          Re: Concatenate Each Row To Cells In Column

          Thanks Dave,

          I'm not receiving any more errors.
          But I definitely have a logic problem

          The final string is correct for the first record (Rows 2-4)
          But not the second record (Rows 5-6)

          Rows 3 and 4 are updating with comments from rows 5 and 6, respectively
          The intent is that rows 2,3,4 should concatentate and be placed in row 2 - which works

          Then 6 should be concatenated onto 5 and place in row 5 - this does not work
          Apparently I'm not updating intRecord correctly. After buggering with it for 2 hours I"m not sure how to.

          Any ideas?
          Updated code below

          Thanks much!
          Regards,
          marc

          bump
          Hi all,
          Does anyone have any ideas as to the code logic below
          I can't figure out what I'm doing wrong
          Thanks!
          marc

          Code:
          Option Explicit
          
          Sub wo_Consolidate_Comments()
              Dim wbBook As Workbook
              Dim wsData As Worksheet
              Dim strComment As String
              Dim rngComment As Range
              Dim lngrows As Long
              Dim intCounter As Integer
              Dim intRecord As Integer
              
              Application.ScreenUpdating = False
              Application.Calculation = xlCalculationManual
              Application.DisplayAlerts = False
              
              Set wbBook = ThisWorkbook
              Set wsData = wbBook.Worksheets("Data")
              lngrows = wsData.Range("A65536").End(xlUp).Row
              intCounter = 2
              intRecord = intCounter
              strComment = wsData.Cells(intCounter, 5)
              Set rngComment = wsData.Cells(intRecord, 5)
              
              Do While lngrows >= 2
                  With wsData
                      If .Cells(intCounter + 1, 1) = .Cells(intCounter, 1) Then
                          strComment = strComment + " " + .Cells(intCounter + 1, 5).Value
                      Else
                          Set rngComment = .Cells(intRecord, 5)
                          intRecord = intCounter
                          rngComment.Value = strComment
                          strComment = ""
                      End If
                      
                      intCounter = intCounter + 1
                      lngrows = lngrows - 1
                  End With
              Loop
              
              Set wbBook = Nothing
              Set wsData = Nothing
              Set rngComment = Nothing
              
              Application.ScreenUpdating = True
              Application.Calculation = xlCalculationAutomatic
              Application.DisplayAlerts = True
               
          End Sub
          Last edited by markc; August 10th, 2008, 00:17.

          Comment


          • #6
            Re: Concatenate Each Row To Cells In Column

            Bump.

            Hi all,
            I've pulled out my hair (what little I had left)
            I just can't figure out what I'm doing wrong here.
            Any ideas?

            See most recent code post above.
            Many thanks,
            marc

            Comment


            • #7
              Re: Concatenate Each Row To Cells In Column

              Hi,

              Try something like..

              Sub kTest()
              Dim i As Long, r As Long, strComm As String, strRng As Range
              r = Range("a" & Rows.Count).End(xlUp).Row
              Set strRng = Cells(2, 5)
              For i = 2 To r
              If Cells(i, 1) <> Cells(i + 1, 1) Then
              strComm = strComm & " " & Cells(i, 5)
              strRng = Mid$(strComm, 2)
              strComm = ""
              Set strRng = Cells(i + 1, 5)
              Else
              strComm = strComm & " " & Cells(i, 5)
              End If
              Next
              End Sub


              HTH
              Kris

              ExcelFox

              Comment


              • #8
                Re: Concatenate Each Row To Cells In Column

                Kris,

                Many thanks!
                Works great!

                Best regards,
                marc

                Comment

                Working...
                X