Help required in Macro to format the Cell

M

mahesh_g

New member
Joined
Jul 28, 2016
Messages
3
Reaction score
0
Points
0
Location
Bangalore,India
Hi,

I am working on a macro, can some one help me in finding the logic.

My requirement I
1. I have a text file which is of Comma separated, which has some number of fields.
2. I need to import the data to sheet-2 from this text file and separate the fields which are delimited by comma.
3. copy the text from sheet-2 and paste the data into sheet 1 by transposing.
4. there are some fields which has multiple values for example 01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
i want this field to be like
01 - TEST CARD1
02 - TEST CARD2
03 - TEST CARD3
04 - TEST CARD4

Note:here the values not constant there might be four as shown in above or 5 or 6... some times there might not be any value (Blank)

can you please help in solving this logic.

I have written a logic to import the data from the text file to sheet-2 and copy the data to the sheet-1 by transposing.

Code: 
[TABLE="width: 500"]
[TR]
[TD]Sub TEst()
'
' TEst Macro
'

' Sheets("Sheet").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\mgandla\Desktop\16.6\TESTING.txt", Destination:=Range("$A$1"))
.Name = "TESTING"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1:C11").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.Select
Range("C8").Activate
Cells.EntireColumn.AutoFit

End Sub[/TD]
[/TR]
[/TABLE]
Input file:
AA,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4
AB,Y,01 - TEST CARD1 02 - TEST CARD2
AC,Y,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AD,Y,
AE,Y,01 - TEST CARD1 62 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5
AF, ,01 - TEST CARD1 02 - TEST CARD2 03 - TEST CARD3
AG,Y,
AH,N,01 - TEST CARD1 15 - TEST CARD2 03 - TEST CARD3
AI,N,01 - TEST CARD1 11 - TEST CARD2 03 - TEST CARD3
AJ, ,01 - TEST CARD1
AK,Y,01 - TEST CARD1 10 - TEST CARD2 03 - TEST CARD3 04 - TEST CARD4 05 - TEST CARD5 06 - TEST CARD6


expected Output
AAABACADAEAFAGAHAIAJAK
YYYYYYNNY
01 - TEST CARD1
02 - TEST CARD2 03 - TEST CARD3
04 - TEST CARD4		01 - TEST CARD1
02 - TEST CARD2		01 - TEST CARD1
02 - TEST CARD2
03 - TEST CARD3		01 - TEST CARD1
62 - TEST CARD2
03 - TEST CARD3
04 - TEST CARD4
05 - TEST CARD5		01 - TEST CARD1
02 - TEST CARD2
03 - TEST CARD3		01 - TEST CARD1
15 - TEST CARD2
03 - TEST CARD3		01 - TEST CARD1
11 - TEST CARD2
03 - TEST CARD3		01 - TEST CARD101 - TEST CARD1
10 - TEST CARD2
03 - TEST CARD3
04 - TEST CARD4
05 - TEST CARD5
06 - TEST CARD6
 
Last edited by a moderator:
Please post a sample sheet so we can work with it. Thanks
 
Hi,

I have provided the input file, which looks like that. and also i have provided the Expected Output file. can you please look into this, and let me know if anything is required
Thanks
 
Last edited by a moderator:
I'm sorry there is no xl sheet attached. You cannot expect members to retype your data.
 
Please find the attached sheet.
Requirement 1 : Input File Test.Txt. (In this the the fields should be divided as below:
01 - TEST CARD1
02 - TEST CARD2 03 - TEST CARD3
04 - TEST CARD4
Requirement 2: Input file Test_Apl.Txt
DM - TEST CARD1
FM - TEST CARD2
OR - TEST CARD3
RF - TEST CARD4
Please let me know if you require more info
 

Attachments

  • Test.txt
    550 bytes · Views: 10
  • Test_Apl.txt
    550 bytes · Views: 6
Last edited by a moderator:
To get you started, run TEst:
Code: 
Sub TEst()
Set yyy = ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Users\mgandla\Desktop\16.6\TESTING.txt", Destination:=Range("$A$1"))
With yyy
  .Name = "TESTING"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 437
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = Array(1, 1, 1)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
End With
For Each cll In yyy.ResultRange.Columns(3).Cells
  basd cll
Next cll
yyy.Delete
Range("A1").CurrentRegion.Copy
Sheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True '<<< adjust sheet name here.
Application.CutCopyMode = False
Sheets("Sheet3").UsedRange.EntireColumn.AutoFit '<<< adjust sheet name here.
End Sub

Sub basd(acell)
zzz = Split(Application.Trim(acell.Value))
myBreak = UBound(zzz)
If myBreak >= 0 Then
  For i = 1 To UBound(zzz)
    If zzz(i) = "-" Then
      If i < myBreak Then
        myBreak = i - 1
      Else
        acell.Offset(, ofset).Value = concat(myBreak, i - 2, zzz)
        ofset = ofset + 1
        myBreak = i - 1
      End If
    End If
  Next i
  acell.Offset(, ofset).Value = concat(myBreak, UBound(zzz), zzz)
End If
End Sub

Function concat(strt, fin, arr)
For j = strt To fin
  concat = concat & arr(j) & " "
Next j
concat = Application.Trim(concat)
End Function
It works on both your .txt files but it's flaky because it depends on the existence hyphens in the right places; it finds the hyphen and assumes that the data needs to be split before the word/number before the hyphen.
 
You must log in or register to reply here.
Back
Top