destination in text to column

[mrinal saha]

Hi,

In below code, i have given the destination = range(b2). But everytime I run the code either the data splits and get pasted in b3 or b4, but not in b2. Any specific reason for this..

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("b2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1), Array(20, 1), Array(28, 1), Array(40, 1), _
Array(51, 1), Array(115, 1), Array(147, 1), Array(179, 1)), TrailingMinusNumbers:= _
True
Columns("A:A").Delete

thanks,
Mrinal

 

[wigi]

Can you post a representative sample of the cells you are splitting using this functionality?

 

[mrinal saha]

example

column A contains the below info

<TABLE style="WIDTH: 1302pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1736 border=0><COLGROUP><COL style="WIDTH: 1302pt; mso-width-source: userset; mso-width-alt: 63488" width=1736><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 1302pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=1736 height=18>PDBD420P-R01 PRODUCT & PRICING DATE 07/26/11 </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18> SECURITY SETUP AUTOMATION - DAILY SETUPS (DEBT SECURITIES) PAGE 1 </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18> CUSIP SEC # PROD.C SEC.SYMBOL MRKT NAME SIRS ISSUER NAME DESCRIPTION LINE 1 DESCRIPTION LINE 2 DESCRIPTION LINE 3 </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> --------- ------ ------ ---------- --------- -------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffcc" height=18> 02000000 N00000 xxx xxx xX abc BANK abc BANK UT ind RT 00.1000% MAT 02/04/13 FIXED RATE CD </TD></TR></TBODY></TABLE>


thanks,
Mrinal