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

 

[skyport]

Thanks Hercules,

1. Data from Col G is the only missing data
2. It is always truncated in K with a greater portion of the last part gone.
3. It is always ok up to a point and then the entire remaining portion of data is not there. No Breaks or off and on symptoms.
4. Where it ends in K will always be different depending on the data in G ( no set pattern). The characters it stops at will be different. Example: On one case, it processed about a half page worth before truncating the remainder. On another case it processed 2 1/2 pages worth before truncating the remaining approx 3 pages.
5. The changes were the only way at that moment seen to do what was needed although if there is a better way, I am all for it. The one and only thing the changes were meant to do was allow for the dates as they show up in K to be in the same format as they actually are in A-G. Example: Dates may be mm/dd/yy or m/d/yy etc throughout A-G. However, without the code change that was made in red, the code would ALWAYS automatically concatenate the result of a date into K as one format only being - mm/dd/yyyy. That was the problem the change in the code overcame. Maybe there is a better way to address that issue that won't cause the current problem as well.

 

[Hercules1946]

Its possible that by using .text instead of .value this is creating a problem because .text shows what appears on the screen in a cell.
This article and others discuss the differences between the two properties, and how you can get more characters depending on the size of your cell and the way it is formatted.

https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/

 

[skyport]

Great article. The following person responding to it seemed to run into the same dating issue I had:

<cite>snb</cite> says:
October 29, 2014 at 9:19 am
If you want to fill comboboxex / listboxes according to the international settings you need the .Text property; otherwise your dates will be mm/dd/yyyy instead of e.g. dd-mm-yyyy or dd.mm.yyyy.
Unless you use .Text , currency will be written as 12.35 instead of e.g. ƒ 12,35
Generally: if you want the content of listboxes, comboboxes, textboxes match the formatting in cells you will have to use .Text (or reformat values in VBA before populating the control).
Reply
·

<cite>fastexcel</cite> says:
October 30, 2014 at 8:23 am
Thanks: another occasion when using .Text might make sense


Can you think of a better way to preserve the proper date format and go back to the original code that worked instead of .text?

 

[Hercules1946]

If I understand you, these dates can occur in any column from A to G?
And the date formats used (in A to G) can vary?
If a cell from A to G has a date in it, is that all there is in the cell?
Would you be able to create sample data for a couple of rows, demonstrating the problem and upload this to a drop box, so I can look at it?

 

[skyport]

I would be happy to do that. Do you have a preferred location for me to drop it?

 

[Hercules1946]

Do you have a preferred location for me to drop it?

Not really... There is one I have used called Dropbox.com. I think you need to register, but you can get a free trial. I think there are a few of these around, and any that you can provide a link to will be OK.

 

[NoSparks]

If you have a gmail account you can use your Google Drive
If you have a Microsoft hotmail.com or outlook.com email account you can use your Sky Drive or One Drive or whatever they call it now.

Box.com is also often suggested on this forum.

 

[Hercules1946]

If you have a gmail account you can use your Google Drive
If you have a Microsoft hotmail.com or outlook.com email account you can use your Sky Drive or One Drive or whatever they call it now.

Cheers for that.

 

[skyport]

This one is through Google

https://docs.google.com/spreadsheets/d/1-BDZA6sTknaQRQB0vdJyxp5AM7Aa1YBB8t_hV1DOZ8Q/edit?usp=sharing

see the Sheet2. Thiis is a good example of data even though it is repeating itself just for the purpose of testing a long entry

 

[Hercules1946]

This one is through Google

Thanks. I will get back to you as soon as I can