Monday, July 12, 2010

how to separate cells value in rows into cells in columns

Software Environment
Version:4.0.2
Edition:Excel

Effect image

Example code
<SelectRange(,"Sheet1","Book1.xls")>

$Address1_Sheet$="C2"
$Address2_Sheet$="A1"
For(4)
$Value$=<ReadCell($Address2_Sheet$)>
$Value$=<TextRight($Value$,3)>
<WriteCell($Value$,$Address1_Sheet$)>
$Address2_Sheet$=<RowAddress($Address2_Sheet$,"Down",3)>
$Address1_Sheet$=<RowAddress($Address1_Sheet$)>
Next

$Address1_Sheet$="D2"
$Address2_Sheet$="A2"
For(4)
$Value$=<ReadCell($Address2_Sheet$)>
$Value$=<TextTrimLeft($Value$,6)>
<WriteCell($Value$,$Address1_Sheet$)>
$Address2_Sheet$=<RowAddress($Address2_Sheet$,"Down",3)>
$Address1_Sheet$=<RowAddress($Address1_Sheet$)>
Next

$Address1_Sheet$="E2"
$Address2_Sheet$="A3"
For(4)
$Value$=<ReadCell($Address2_Sheet$)>
$Value$=<TextRight($Value$,1)>
<WriteCell($Value$,$Address1_Sheet$)>
$Address2_Sheet$=<RowAddress($Address2_Sheet$,"Down",3)>
$Address1_Sheet$=<RowAddress($Address1_Sheet$)>
Next


Code explanation
$Address1_Sheet$; Save cell address in column.
$Address2_Sheet$; Save cell address in row.
$Value$; Save the matching value.

The first For loop separate ID value in row into cells in column.
The second For loop separate Name value in row into cells in column.
The third For loop separate Sex value in row into cells in column.