gmaxey
08-10-2019, 10:52 AM
Hi Excel Gurus,
This issue is related to an earlier post "Suggestion for Best Practice"
http://www.vbaexpress.com/forum/showthread.php?65633-Suggestion-for-Best-Practice
I can't seem to be able to make .TextToColumns work consistently. The following code provides an example of the issue. Just paste it in a new workbook:
When you first run it, it performs exactly as I would expect. The text in rows 11, 12 and 13 delimited using a tab is split between columns B and C. No other text in columns 1 - 10 is moved to column C
However if Line 1 is stetted and Line 2 is unstetted and the code is run. It all falls apart.
Hoping someone can help me understand what is happening and show how split only text delimited with a tab in rows (after row 9) between columns B and C.
Thanks.
Sub DEMOISSUE()
Dim lngCC As Long, lngIndex As Long
Dim varCCs, varAttrs, varTitles
Dim oCol
ReDim varCCs(0)
ReDim varAttrs(9)
varAttrs(0) = "123456"
varAttrs(1) = "3"
'Here are three scenarios
1 varAttrs(2) = "My Title" 'Works perfectly. The text delimited in column B with a tab (rows 11, 12 and 13) is split between column B and C
'Stet line 1 and unstet line 2.
2 'varAttrs(2) = "My Title One Two Three" 'Falls apart. Text in rows 3 and 7 are split between columns B, C and D in some seemingly random
'fashion. The text in rows 11, 12 and 13 are not split at all!!
'Set the line above and unstet the next line.
3 'varAttrs(2) = "My Title Three Four Seven" 'Works perfectly. The tab delimited text in 11, 12 and 13 in column B again is split between column B and C ???
varAttrs(3) = "My Tag"
varAttrs(4) = "True"
varAttrs(5) = "True"
varAttrs(6) = "Choose an Item" 'This is a normal condition.
varAttrs(7) = " "
varAttrs(8) = " "
varAttrs(9) = "PHT" & vbTab & "" & vbLf
For lngIndex = 1 To 3
varAttrs(9) = varAttrs(9) & "A" & vbTab & "Alpha" & vbLf
Next lngIndex
varAttrs = Split(Join(varAttrs, vbLf), vbLf)
varCCs(0) = varAttrs
If Not Sheets("Sheet1").Range("A1").Value = "Content control ID" Then
'Clear the sheet and add legend.
Sheets("Sheet1").Cells.ClearContents
varTitles = Split("Content control ID|Type|Title|Tag|Contents cannot be edited|Content control cannot be deleted|Placeholder Text" _
& "|Temporay/BB Gal/Date Format|Multi-Line/BB Cat|List Entries", "|")
Sheets("Sheet1").Cells(1, 1).Resize(UBound(varTitles) + 1).Value = Application.Transpose(varTitles)
Else
'Clear sheet except for legend.
Sheets("Sheet1").UsedRange.Offset(, 2).ClearContents
End If
'Write the CC data to the sheet.
For lngCC = 0 To UBound(varCCs)
'Data is writen to single columns separated by an empty column
With Sheets("Sheet1").Cells(1, 2 + 2 * lngCC).Resize(UBound(varCCs(lngCC)) + 1)
.Value = .Application.Transpose(varCCs(lngCC))
'Split the list entries delimited with vbTab into two columns.
.TextToColumns Tab:=True, Space:=False
End With
Next
Sheets("Sheet1").Columns.AutoFit
'Remove remaining empty columns.
For lngIndex = Sheets("Sheet1").UsedRange.Columns.Count To 1 Step -1
Set oCol = Sheets("Sheet1").UsedRange.Cells(1, lngIndex).EntireColumn
If Application.WorksheetFunction.CountA(oCol) = 0 Then
oCol.Delete
End If
Next lngIndex
lbl_Exit:
Exit Sub
End Sub
This issue is related to an earlier post "Suggestion for Best Practice"
http://www.vbaexpress.com/forum/showthread.php?65633-Suggestion-for-Best-Practice
I can't seem to be able to make .TextToColumns work consistently. The following code provides an example of the issue. Just paste it in a new workbook:
When you first run it, it performs exactly as I would expect. The text in rows 11, 12 and 13 delimited using a tab is split between columns B and C. No other text in columns 1 - 10 is moved to column C
However if Line 1 is stetted and Line 2 is unstetted and the code is run. It all falls apart.
Hoping someone can help me understand what is happening and show how split only text delimited with a tab in rows (after row 9) between columns B and C.
Thanks.
Sub DEMOISSUE()
Dim lngCC As Long, lngIndex As Long
Dim varCCs, varAttrs, varTitles
Dim oCol
ReDim varCCs(0)
ReDim varAttrs(9)
varAttrs(0) = "123456"
varAttrs(1) = "3"
'Here are three scenarios
1 varAttrs(2) = "My Title" 'Works perfectly. The text delimited in column B with a tab (rows 11, 12 and 13) is split between column B and C
'Stet line 1 and unstet line 2.
2 'varAttrs(2) = "My Title One Two Three" 'Falls apart. Text in rows 3 and 7 are split between columns B, C and D in some seemingly random
'fashion. The text in rows 11, 12 and 13 are not split at all!!
'Set the line above and unstet the next line.
3 'varAttrs(2) = "My Title Three Four Seven" 'Works perfectly. The tab delimited text in 11, 12 and 13 in column B again is split between column B and C ???
varAttrs(3) = "My Tag"
varAttrs(4) = "True"
varAttrs(5) = "True"
varAttrs(6) = "Choose an Item" 'This is a normal condition.
varAttrs(7) = " "
varAttrs(8) = " "
varAttrs(9) = "PHT" & vbTab & "" & vbLf
For lngIndex = 1 To 3
varAttrs(9) = varAttrs(9) & "A" & vbTab & "Alpha" & vbLf
Next lngIndex
varAttrs = Split(Join(varAttrs, vbLf), vbLf)
varCCs(0) = varAttrs
If Not Sheets("Sheet1").Range("A1").Value = "Content control ID" Then
'Clear the sheet and add legend.
Sheets("Sheet1").Cells.ClearContents
varTitles = Split("Content control ID|Type|Title|Tag|Contents cannot be edited|Content control cannot be deleted|Placeholder Text" _
& "|Temporay/BB Gal/Date Format|Multi-Line/BB Cat|List Entries", "|")
Sheets("Sheet1").Cells(1, 1).Resize(UBound(varTitles) + 1).Value = Application.Transpose(varTitles)
Else
'Clear sheet except for legend.
Sheets("Sheet1").UsedRange.Offset(, 2).ClearContents
End If
'Write the CC data to the sheet.
For lngCC = 0 To UBound(varCCs)
'Data is writen to single columns separated by an empty column
With Sheets("Sheet1").Cells(1, 2 + 2 * lngCC).Resize(UBound(varCCs(lngCC)) + 1)
.Value = .Application.Transpose(varCCs(lngCC))
'Split the list entries delimited with vbTab into two columns.
.TextToColumns Tab:=True, Space:=False
End With
Next
Sheets("Sheet1").Columns.AutoFit
'Remove remaining empty columns.
For lngIndex = Sheets("Sheet1").UsedRange.Columns.Count To 1 Step -1
Set oCol = Sheets("Sheet1").UsedRange.Cells(1, lngIndex).EntireColumn
If Application.WorksheetFunction.CountA(oCol) = 0 Then
oCol.Delete
End If
Next lngIndex
lbl_Exit:
Exit Sub
End Sub