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]

Hello Skyport
I thought Id update you. I spent a bit of time looking at this problem with the data you provided.
1. Im happy that I can do the concatenate without any loss of data in column K.
2. There is of course a problem displaying the data on the screen in Excel because of the amount of characters, but it is all there in the cell.
3. With the dates, I can't see any problem with those from Col G as they look exactly the same when viewed in K. However, Col E1 has a date format, and what appears in the formula bar will be based on the
regional format provided by the computer that created it (rather than Excel itself). What appears in the cell display is whatever text format you want. There are ways to reformat both of these.
4. I hope to provide some code for you later today, and if you can explain exactly how you want to use/display the data in K and say what date format you want to see, I can (hopefully) do that.

 

[Hercules1946]

With the data you gave me this code seems to work. I had to change the "bolding" task, because it was picking up "Dr. " as end of sentence.
Give this a try and let me know how you get on.

Sub Concat()
Dim i As Long, LR As Long, j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Sheets(2).Activate
Columns("K:K").Select
With Selection.Font
    .FontStyle = "Regular"
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 2
Range("K" & i) = Range("A" & i).Text & Range("B" & i).Text & Range("C" & i).Text & Range("D" & i).Text & Range("E" & i).Text & Range("F" & i).Text & Range("G" & i)
With Range("K" & i)
j = InStr(.Value, ".  ") 'two spaces
.Characters(Start:=1, Length:=j).Font.Bold = True
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[skyport]

Hercules, I believe you may have solved what no one else seemed to be able to do. It seems to work so far when it is applied to sheet 2 as indicated in your code. I need to see if it can work on the other sheets as well. I tried substituting sheet 2 in the code with another sheet name and it did not work. Shouldn't be difficult to resolve I would think. Any ideas on this? Below is the change I made which did not work for the worksheet page titled "concate"

Sheets(2).Activate


changed to:


Sheets(concate).Activate

 

[MARK858]

Quotation marks?

Sheets("concate").Activate

 

[skyport]

Mark, Thanks so much for that input

 

[skyport]

Tried it with the quote marks and I get a "compile error expected end with" and along with the error message it highlights the last line in the code window being " end sub"

 

[MARK858]

Then you will need to post the full code as you have it as you are probably missing or have in the wrong place either an end with or end if.

 

[Hercules1946]

I get a "compile error expected end with" and along with the error message it highlights the last line in the code window being " end sub"

Hello Skyport
Im pleased that we have almost resolved your problems.

1. Sheets() command.
When Excel provides sheets for you, these are numbered sequentially (by Excel) in order of creation. If you then decide to provide your own name 'concate" for the second one, then this is displayed instead of 'sheet2' on the screen. However, Excel retains its own name as well. If you look at the Project Explorer window in VBA you will see that each sheet has two names : Sheet1(Sheet1), Sheet2(Sheet("concate"). With the Sheets() function you can use either, but if you use your own name, it needs to be inside double quotes, as it becomes alpha-numeric like so: Sheets(2) or Sheets("concate").

2. Compile error
From what you describe, the code has reached End Sub, without having Exited a With loop first. This can occur if you have tried to edit out a With loop,
but you have wissed the With statement itself. I reposted the code, but using your "concate" worksheet. Remember, editing out a loop is like removing a tooth.. You'll have problems if
you don't get it all !

3. Missing Character problem
This was happening because the .Text property has a limit of 8221 characters. My guess would be that its a data buffer that hasn't been increased as later editions of
Excel have become able to deal with larger blocks of text in one cell. There is a similar restriction applied to characters in a formula, but 8192 should be enough for most

Sub Concat()
Dim i As Long, LR As Long, j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Sheets("concate").Activate
Columns("K:K").Select
With Selection.Font
    .FontStyle = "Regular"
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Range("K" & i) = Range("A" & i).Text & Range("B" & i).Text & Range("C" & i).Text & Range("D" & i).Text & Range("E" & i).Text & Range("F" & i).Text & Range("G" & i)
With Range("K" & i)
j = InStr(.Value, ".  ") 'two spaces
.Characters(Start:=1, Length:=j).Font.Bold = True 'Sets the whole cell as bold for next time
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Good luck, and come back to us if there is anything further.

 

[skyport]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> Good morning Hercules.


I want to thank you very much for sticking with this and providing the magnificent solution to the problem. I have notated that you are in the good old United Kingdom, which has always been dear to my heart. I believe I will celebrate this victory with my favorite cup of tea from your part of the world. One of the wonderful things about this site is the connection of help and assistance with friendship we can establish internationally in this complicated world. This case was certainly an example of such and you have my tremendous appreciation for your expertise and help. I tested the code on different pages under different names with success and I was even able to alter and change the column in which to place the result in different columns and it all seems to work magnificently. I found it very impressive that this poser was presented from different directions without anyone able to resolve the issue until you took the time to step forward with a solution. For this I thank you for your time and effort in helping me when no one else seemed to be able to step forward with a workable solution. For what it is worth, although you may be recognized as a board regular on the site, because of your effort and solution, I recognize you as an MVP status, which hopefully others of significance will recognize as well. The site certainly needs more of those with expertise and willing to help such as yourself. I will ask one favor in conclusion and that being, as I further apply the code to the various challenges over the next week or so, if I should by some chance run into some challenge that was unforeseen, could you please monitor this thread for at least a week or so to see if any surprise questions or issues may present themselves. If you would be kind enough to do so I would be most grateful. Within that timeframe, I will give you a report if no problems have incurred, which at this point I do not anticipate. However, it would be nice to know I have a friend that would be there in the event such were to occur. I thank you again for all your help and comend your efforts and solution.

 

[skyport]

I would also like to thank Mark for following along on this with his input.