Excel question

[freggel99]

Hi there...

I have a macro and one problem.. the macro checks for the length of the numbers and if they are too short it lengthens them by adding a zero... what I want is that if the number is changed the number is replaced in the excelsheet...

this is my macro so far....

out
ARRAY(str) a
ExcelSheet es.Init
es.GetCells(a) ;;gets all cells. To get only selected cells, use this: es.GetCells(a "sel")
int r c
for r 0 a.len
,if(mes("Row %i" "" "OC" r+1) != 'O') break
,for c 0 a.len(1)
,,str& s=a[c r]
,,str s1
,,if(findrx(s "\d\d\d" 0 16 s1)>0) mes s1
,,out s
,,out s.len
,,if s.len=3
,,,s - "0"

any help is appreciated...

tia

[Gintaras]

Adds 0 if cell text length is <3.
1 -> 001
2 -> 002

Macro

Code:

Copy      Help

;/exe 1
out
str sn="0000000000"
ARRAY(str) a
ExcelSheet es.Init
es.GetCells(a) ;;gets all cells. To get only selected cells, use this: es.GetCells(a "sel")
int r c
for r 0 a.len
,if(mes("Row %i" "" "OC" r+1) != 'O') break
,for c 0 a.len(1)
,,str& s=a[c r]
,,int x=3-s.len
,,if(x<=0) continue
,,s.fromn(sn x s s.len)
,,out s
,,es.SetCell(s c+1 r+1)

[freggel99]

thank you very much!

although when i input the line for the selected area [es.GetCells(a "sel")]

the macro works but in the wrong column... in the column next to it... so if column b is selected a is edited...

any ideas?

[Gintaras]

Macro

Code:

Copy      Help

;/exe 1
out
str sn="0000000000"
ARRAY(str) a
ExcelSheet es.Init
es.GetCells(a "sel") ;;gets selected cells

;this code gets 1-based row and column index of top-left cell of selection
Excel.Range sr=es.ws.Application.Selection
int sx(sr.Column) sy(sr.Row)

int r c
for r 0 a.len
,if(mes("Row %i" "" "OC" r+1) != 'O') break
,for c 0 a.len(1)
,,str& s=a[c r]
,,int x=3-s.len
,,if(x<=0) continue
,,s.fromn(sn x s s.len)
,,out s
,,es.SetCell(s c+sx r+sy)