In Excel,
[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1234[/TD]
[TD]035=Yes[/TD]
[TD]102=Yes[/TD]
[TD]100=70[/TD]
[TD]097=Yes[/TD]
[TD]Sometext shouldcome after a longspace thankyou[/TD]
[/TR]
[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1235[/TD]
[TD]035=No[/TD]
[TD]102=No[/TD]
[TD]100=71[/TD]
[TD]097=No[/TD]
[TD]This is second scenario thankyou[/TD]
[/TR]
</tbody>[/TABLE]
And the OUTPUT should be in NOTEPAD,
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#Sometext shouldcome after a longspace thankyou
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#This is second scenario thankyou
And the script which I tried is,
Sub Characterpostion()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim str As String
Dim cellvalue As String
LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
FilePath = FreeFile
CellData = ""
Open "C:\Users\Antony\Music\Excel Macros\Text2.txt" For Output As #2
For i = 1 To LastRow
For j = 1 To LastCol
Select Case j
Case 1
n = 5 - Len(Trim(ActiveCell(i, j).Value))
Case 2
n = 3 - Len(Trim(ActiveCell(i, j).Value))
Case 3
n = 8 - Len(Trim(ActiveCell(i, j).Value))
Case 4
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 5
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 6
n = 6 - Len(Trim(ActiveCell(i, j).Value))
Case 7
n = 7 - Len(Trim(ActiveCell(i, j).Value))
End Select
CellData = CellData & Space & Trim(ActiveCell(i, j).Value) & "@#"
If CellData Like "*=*" Then
Dim WrdArray() As String
WrdArray() = Split(CellData, "=")
str = WrdArray(0) + WrdArray(1)
Print #2 , str
Else
End If
Next j
Print #2 , CellData
CellData = ""
Next i
Close #2
End Sub
But CellData stores all the case at a time... I am not able to null the CellData to restore the new value.
Appreciate your immediate response!! thanks
[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1234[/TD]
[TD]035=Yes[/TD]
[TD]102=Yes[/TD]
[TD]100=70[/TD]
[TD]097=Yes[/TD]
[TD]Sometext shouldcome after a longspace thankyou[/TD]
[/TR]
[TR]
[TD]AN201[/TD]
[TD]BOS[/TD]
[TD]306=1235[/TD]
[TD]035=No[/TD]
[TD]102=No[/TD]
[TD]100=71[/TD]
[TD]097=No[/TD]
[TD]This is second scenario thankyou[/TD]
[/TR]
</tbody>[/TABLE]
And the OUTPUT should be in NOTEPAD,
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#Sometext shouldcome after a longspace thankyou
AN201@#BOS@#3061234@#035Yes@#102Yes@#10070@#097Yes@#This is second scenario thankyou
And the script which I tried is,
Sub Characterpostion()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim str As String
Dim cellvalue As String
LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
FilePath = FreeFile
CellData = ""
Open "C:\Users\Antony\Music\Excel Macros\Text2.txt" For Output As #2
For i = 1 To LastRow
For j = 1 To LastCol
Select Case j
Case 1
n = 5 - Len(Trim(ActiveCell(i, j).Value))
Case 2
n = 3 - Len(Trim(ActiveCell(i, j).Value))
Case 3
n = 8 - Len(Trim(ActiveCell(i, j).Value))
Case 4
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 5
n = 7 - Len(Trim(ActiveCell(i, j).Value))
Case 6
n = 6 - Len(Trim(ActiveCell(i, j).Value))
Case 7
n = 7 - Len(Trim(ActiveCell(i, j).Value))
End Select
CellData = CellData & Space & Trim(ActiveCell(i, j).Value) & "@#"
If CellData Like "*=*" Then
Dim WrdArray() As String
WrdArray() = Split(CellData, "=")
str = WrdArray(0) + WrdArray(1)
Print #2 , str
Else
End If
Next j
Print #2 , CellData
CellData = ""
Next i
Close #2
End Sub
But CellData stores all the case at a time... I am not able to null the CellData to restore the new value.
Appreciate your immediate response!! thanks