Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Concatenate Each Row To Cells In Column

  1. #1
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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
    VB:
    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

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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?

  3. #3
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    Re: Concatenate Each Row To Cells In Column

    Thanks Dave,

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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.

  5. #5
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    VB:
    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 at 00:17.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Concatenate Each Row To Cells In Column

    Hi,

    Try something like..

    VB:
    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

  8. #8
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    Re: Concatenate Each Row To Cells In Column

    Kris,

    Many thanks!
    Works great!

    Best regards,
    marc

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Concatenate Column With Adjacent Column In Loop
    By chrislayfield in forum Excel General
    Replies: 9
    Last Post: September 28th, 2007, 12:16
  2. Concatenate Whole Column To Row
    By vishal_341 in forum Excel General
    Replies: 5
    Last Post: May 24th, 2007, 18:37
  3. Concatenate Column With Row Value
    By nisar in forum Excel General
    Replies: 12
    Last Post: September 29th, 2006, 04:56
  4. CONCATENATE a Column
    By Heath H in forum Excel General
    Replies: 8
    Last Post: November 18th, 2004, 03:16
  5. Concatenate values in a column of cells
    By braveheart in forum Excel General
    Replies: 5
    Last Post: June 9th, 2004, 03:27

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno