Please Help me with this VBA

[skyport]

This VBA code I am using has a problem I hope someone can help me with. The code is posted below. 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 and preserves the way dates are actually appearing from the source of the data (columns A-G). It actually works well with the exception that concerning the data in column G, the code seems to only be processing the first part of that data from each cell of column G into column K and truncating or cutting off the remainder of the data. Example: G4 may have 6 or 7 pages of text within that cell. However, the result that shows up in K4 is only 2 ½ pages and cut off in mid sentence. I am hoping someone can help with this.


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

 

[Hercules1946]

I tested this by putting the narrative fom #1 into A1 to G1, which is a total of 766 characters and that worked fine. Can you place a formula as below somewhere after you have run the code and post the figure, please?

=LEN(Kn) where n = any row number where there is missing text from Col G.

 

[skyport]

Hi Hercules, thanks for helping me with this.

When I entered the formula in cell 2 of Column L it returned the following - " #Name? "

One of the cells with more data in from column G shows that it has 16,552 characters if that helps

 

[skyport]

my goof, the formula result you asked for would be 1044

 

[MARK858]

@skyport, what version of Excel are you using and does the formula bar also cut off the data?

 

[skyport]

Hi Mark, it is 2007 and yes the formula bar cuts it off as well. Here is an interesting twist which may present a clue. The original formula I was using was identical to this current one and it worked perfectly with nothing cut off. The only thing that was changed was the line below in order to recognize the dates in their actual appearance as they appeared in columns A-G by changing "value" to "text". ( see comparison below ) There was no other change to the code.

Original version

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



Current Version ( only changes made were in section 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

 

[skyport]

Help!!! Does anyone have a solution for this one???

 

[Hercules1946]

my goof, the formula result you asked for would be 1044

The reason I asked you to test with the LEN() function is because one cell can only hold a maximum of 32767 characters. If col G alone has 16552, then the combined content from A to G may exceed this. Are you saying that the one in #4 had only 1044 chrs in col K after running the macro and was still missing some text from G ? If so then there must be another explanation.

 

[skyport]

Thanks for replying. Columns A-F will never have more than 60 - 70 characters per cell. Cells in column G do not have more than 25,000, so that should not be the issue I would think. There must be something in the way the alteration was made as shown in posting #6 because it never cut anything off with the original version. The problem started to occur after the change in red was made for the purpose of recognizing the date formats as they exist in columns A-G.

 

[Hercules1946]

The problem started to occur after the change in red was made for the purpose of recognizing the date formats as they exist in columns A-G.

I think that the extra information about the code change is a strong clue to finding the explanation. I appreciate that this is very frustrating for you, but without being able to see an example of the offending data, its going to be a bit "hit and miss" trying to solve it. I think that the best approach is a bit of detective work. Questions:

1. I think you said that it was Col G which only had part of its data added to Col K. So Cols A to F are always added in full ?
2. Is part of Col G always missing or just on some rows with other ones working OK ?
3. When the problem arises does so much data get added to K and then no more for that row, or is it missing a "chunk", continuing and missing another chunk etc ?
4. Have you tried pinpointing the last character added, and see if there is anything odd/different about what comes next ?
5. Are the changes doing what is expected, and are they a "must have"?

With 4 above, it might be that your data contains "control characters". These are special characters that are not part of the text, but they are added to (e.g.) alter the way it looks. The key thing is that if other software encounters them, the effect might be unpredictable. If your familiar with BB code I'm using control characters to make this red, and then back to normal. The fact that you have altered your code to merge based on .text in place of .Value makes this a good bet, but we need to establish what is causing it.