VBA problem, need help with existing code

[skyport]

I had been using a code successfully for some time and the other week I had a minor change made to it in order to overcome one problem, which it did perfectly well however, in the process it created a second problem. What the code does is concatenate columns A-G into K but what makes it unique is it bolds the first sentence of each cell in the result column K. Below see example code 1 as the original code that performs this function perfectly. With that in mind, the first problem I overcame was that as of a week ago I needed the results in column K to recognize the text values of dates from columns A- G and so the changes were made in the code as shown in the example code 2 below. This solved the problem however created a new problem where the results in column K are now incomplete and the part of the data from column G when concatenated into K is truncated whereas before the results in column K showed the complete information that was in columns A-G. The only changes that were made between the two versions of the code was inserting "TEXT" to replace "value" on the one line of the code. I absolutely cannot figure out why with that one change it is truncating the result in column K. As a final illustration, prior to the change in the code, I could have several word document pages of information existing in the columns A-G that would all perfectly show up in column K however after the change in the code it will truncate after about two pages worth of data in a given cell in the result column K which never was a problem before. I absolutely cannot figure out why that minor change is causing that problem and I am hoping somebody can help with the solution.

Example code 1

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("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

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



Example code 2
Changes made are in red

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("K" & i)
.Value = .Offset(0, -10).Text & .Offset(0, -9).Text & .Offset(0, -8).Text & _
.Offset(0, -7).Text & .Offset(0, -6).Text & .Offset(0, -5).Text & .Offset(0, -4).Text

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


In conclusion, something in that change above solved the one problem while creating the second one. Hoping someone can help resolve. Thanks

 

[skyport]

Bump