Please Help me with this VBA

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Hercules1946

Well-known Member
Joined
Oct 6, 2007
Messages
545
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

Active Member
Joined
Aug 3, 2014
Messages
374
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,848
Office Version
365, 2010
Platform
Windows, Mobile
@skyport, what version of Excel are you using and does the formula bar also cut off the data?
 

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
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

Active Member
Joined
Aug 3, 2014
Messages
374
Help!!! Does anyone have a solution for this one???
 

Hercules1946

Well-known Member
Joined
Oct 6, 2007
Messages
545
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

Active Member
Joined
Aug 3, 2014
Messages
374
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

Well-known Member
Joined
Oct 6, 2007
Messages
545
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,925
Messages
5,483,735
Members
407,406
Latest member
ishipra

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top