locked
Need VBA help with my program RRS feed

  • Question

  • Please help.  I am using Windows 7 and Excel 2000 while running the program illustrated below, which works except one major problem being:  If the data combined from the columns ending up in column D is more than a couple hundred characters,  it returns the following problem:

     

    run-time error -2147417848 (80010108)':

     

    automation error

    the object invoked has disconnected from it's clients.

     

    When I shorten the amount of text to a couple hundred characters, it works fine but I need to be able to have a lot more than that,.

     

    Below is the program I am using.  All I need is how to allow the recipient column D to contain the maximum number of characters possible because I need to have pages of data concatenating there into that column.  If someone could help me by indicating what I should add or change to the program, I would appreciate it very much.

     

    Sub Concat()

    Dim i As Long, LR As Long, j As Long

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

     

    LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LR

        With Range("D" & i)

            .Value = Join(Application.Transpose(Application.Transpose(.Offset(, -3).Resize(, 3))), vbNullString)

            j = InStr(.Value, ".  ")

            .Characters(Start:=1, Length:=j).Font.Bold = True

        End With

    Next i

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

     

    End Sub


    Sunday, August 3, 2014 9:09 AM

Answers

  • Does this work better for you?

    Sub Concat()
        Dim i As Long, LR As Long, j As Long
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With Range("D" & i)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value
                j = InStr(.Value, ".  ")
                .Characters(Start:=1, Length:=j).Font.Bold = True
            End With
        Next i
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by danishani Friday, September 19, 2014 5:37 AM
    Sunday, August 3, 2014 9:46 AM
  • Change the two lines

            With Range("D" & i)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value

    to

            With Range("K" & i)
                .Value = .Offset(0, -10).Value & .Offset(0, -9).Value & .Offset(0, -8).Value & _
                    .Offset(0, -7).Value & .Offset(0, -6).Value & .Offset(0, -5).Value & .Offset(0, -4).Value
    

    or alternatively (perhaps more readable)

            With Range("K" & i)
                .Value = Range("A" & i).Value & Range("B" & i).Value & Range("C" & i).Value & _
                    Range("D" & i).Value & Range("E" & i).Value & Range("F" & i).Value & Range("G" & i).Value
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by danishani Friday, September 19, 2014 5:37 AM
    Sunday, August 3, 2014 8:49 PM

All replies

  • Does this work better for you?

    Sub Concat()
        Dim i As Long, LR As Long, j As Long
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With Range("D" & i)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value
                j = InStr(.Value, ".  ")
                .Characters(Start:=1, Length:=j).Font.Bold = True
            End With
        Next i
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by danishani Friday, September 19, 2014 5:37 AM
    Sunday, August 3, 2014 9:46 AM
  • Hans,  Thank you so much.  It works Perfectly.  One last question;   the program currently works with data in the first 3 columns - A-C and deposits the result in column D.    How would the program change or look if I needed it to work with data in the columns A through G  and deposit results in Column K.  Thanks again in advance for your kind help.
    Sunday, August 3, 2014 8:37 PM
  • Change the two lines

            With Range("D" & i)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value

    to

            With Range("K" & i)
                .Value = .Offset(0, -10).Value & .Offset(0, -9).Value & .Offset(0, -8).Value & _
                    .Offset(0, -7).Value & .Offset(0, -6).Value & .Offset(0, -5).Value & .Offset(0, -4).Value
    

    or alternatively (perhaps more readable)

            With Range("K" & i)
                .Value = Range("A" & i).Value & Range("B" & i).Value & Range("C" & i).Value & _
                    Range("D" & i).Value & Range("E" & i).Value & Range("F" & i).Value & Range("G" & i).Value
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by danishani Friday, September 19, 2014 5:37 AM
    Sunday, August 3, 2014 8:49 PM
  • Hans,  My many thanks yet again.   It is perfect.   If you are ever in San Diego,  I owe you lunch.  :) 
    Sunday, August 3, 2014 9:40 PM