Need VBA help with my program

[skyport]

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

 

[mole999]

have a read through this Run Time error

there is also a limit on the amount of text that can be put in a cell

 

[JLGWhiz]

A cell can hold over 32k characters, but only 1024 will be displayed in the cell. The formula bar will display all of them. However, When filling the cell with VBA code, it will not automatically truncate when it reaches the 32K + limit. Instead it gives you the error message. You have to write the code to measure how much you are loading into a cell and tell VBA when to stop loading, or to swithch to a different cell. If you cannot satisfy your needs within the Excel limitations, you are probably using the wrong application for what you want to do, or the desired format will need to be adjusted to comply with the limitations of the application.

 

[skyport]

The 32k limit to a cell should be fine for me. The problem apperas to be in the VBA program that limits it to 255 per cell. Maybe someone could tell me what needs to be changed in the program or added.

 

[shg]

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

If that doesn't work, try assigning the Join result to a string variable to start tracking down the problem.

 

[GWteB]

If no special formats are used (date, currency or UDF) you could try the use of Value2 (no warranties) ...