Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a transaction table containing theese records:
(Sorry for bad formating)
period lime_itemno lime_qty
1 66-HRG22-1 267
2 42-JRD22-1 267
2 42-DRGGR-1 800
3 42-RRE2-1 267
3 42-DRG6-1 100
3 42-DRG6-1 100
How do i write a macro that steps through all theese rows, one by one? As you can see, some rows are duplicated, but I still concider theese rows to be unique and I want to step through theese as well.
I've tried this:
sub test
set period = ActiveDocument.Fields("trans_period").GetPossibleValues(100000)
for i = 0 to period.count -1
set itemno = ActiveDocument.Fields("lime_itemno").GetPossibleValues(100000)
for j = 0 to itemno.count -1
set qty = ActiveDocument.Fields("lime_qty").GetPossibleValues(100000)
for k = 0 to qty.count -1
msgbox period(i).text + "-" + itemno(j).text + "-" + qty(k).text
next
next
next
end sub
But that don't work, since I get too many messageboxes, one for each unique combination of all three columns.
Thanks,
/ Robert
Hi
Consider adding a unique rownumber to the transaction table, then create a tablebox with all the 4 fields. Rownumber as the 4th column.
This will now show all 6 rows.
Then use the following macro to loop through the rows (excluding the 4th column with the rownumber).
I would also use a different separator since the itemNo field has - characters in it.
sub test
set TableBox = ActiveDocument.GetSheetObject( "TB01" )
for RowIter = 1 to TableBox.GetRowCount-1
concatText = ""
for ColIter =0 to TableBox.GetColumnCount-2 'Don't include the last column (row)
set cell = TableBox.GetCell(RowIter,ColIter)
concatText = concatText + cell.Text + ";"
next
concatText = left(concatText, len(concatText)-1) ' Remove the trailing ;
msgbox(concatText)
next
end sub
Hope it's what you we're looking for.
/Fredrik
Hi
Consider adding a unique rownumber to the transaction table, then create a tablebox with all the 4 fields. Rownumber as the 4th column.
This will now show all 6 rows.
Then use the following macro to loop through the rows (excluding the 4th column with the rownumber).
I would also use a different separator since the itemNo field has - characters in it.
sub test
set TableBox = ActiveDocument.GetSheetObject( "TB01" )
for RowIter = 1 to TableBox.GetRowCount-1
concatText = ""
for ColIter =0 to TableBox.GetColumnCount-2 'Don't include the last column (row)
set cell = TableBox.GetCell(RowIter,ColIter)
concatText = concatText + cell.Text + ";"
next
concatText = left(concatText, len(concatText)-1) ' Remove the trailing ;
msgbox(concatText)
next
end sub
Hope it's what you we're looking for.
/Fredrik
Elegant!
This script is perfect!
Thank You very much!
/Robert