In the following script I simplified my actual problem:
tell application “Microsoft Excel”
Activate
Select Range “R1C1:R10C1”
copy {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} to myList
copy items of myList to Value of Selection
end tell
All cells now have the value 1. If I apply the script to cells of a row everything is Ok.
So, how can I assign values to a series of column cells without repeats because I have to enter thousands of cells in about 10 columns
TIA
Not terribly intuitive but you have to use a list of lists, not a list of values:
tell application "Microsoft Excel"
Select Range "R1C1:R10C1"
set myList to {{1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}}
set Value of Selection to myList
end tell
The reasoning of this is that each sub-list is an array for filling in the current row. Since you have only selected one column, each row is only one cell so your sub-lists have only one value. If you had multiple columns & rows, then you would have multiple values in the sub-lists. Each sub-list value would equal the row value per column and each sub-list itself would equal the row number. For example:
set the_data to {}
repeat with i from 1 to 10
set this_row to {}
repeat with j from 1 to 10
set end of this_row to (((i as string) & (j as string)) as integer)
end repeat
set end of the_data to this_row
end repeat
--return the_data
tell application "Microsoft Excel"
Activate
Select Range "R1C1:R10C10"
set Value of Selection to the_data
end tell
Hope that helps.
Jon