Hi,
Using Excel 2008 and I am trying to insert ‘X’ number of cells into a column where ‘X’ is a value equal to the difference between two other cells (e.g. - X=B1-A1). Does anyone have any tricks?
Thanks
tell application "Microsoft Excel"
my InsertCellsIntoColumn(range "b1", 2)
end tell
on InsertCellsIntoColumn(startCell, countOfInsertedCells)
tell application "Microsoft Excel"
insert into range (get resize startCell row size countOfInsertedCells column size 1) shift shift down
end tell
end InsertCellsIntoColumn
I re-read the OP and caught the “difference between two cells” part.
I also altered the method to return the inserted cells (or false if the insert fails, eg insert negative number of cells or insert into a protected sheet)
tell application "Microsoft Excel"
set CellsToInsert to (get value of range "b1") - (get value of range "a1")
set InsertedCells to my InsertCellsIntoColumn(get range "C1", CellsToInsert)
if not InsertedCells = false then
set value of InsertedCells to "XYZ"
end if
end tell
on InsertCellsIntoColumn(startCell, countOfInsertedCells)
tell application "Microsoft Excel"
try
insert into range (get resize startCell row size countOfInsertedCells column size 1) shift shift down
return get resize startCell row size countOfInsertedCells
on error
return false
end try
end tell
end InsertCellsIntoColumn
Wow, that worked great. What if I want the inserted rows to shift everything down by the number of inserted cells? In other words, how does one set the value of A in the following line to equal the CellsToInsert?
insert into range (get resize startCell row size A) shift shift down
Thanks for the help. I’m very new to this, but am learning quite a bit from very helpful people. I tailored the script to do what I want it to do, but can’t quite get that last part figured out.
The method (that’s Apple Script’s term for subroutine) InsertCellsIntoColumn has two arguments.
on InsertCellsIntoColumn(startCell, countOfInsertedCells)
The first argument, startCell, is where the cells will be inserted. (B1 in the first script, C1 in the second)
The second argument, countOfInsertedCells, is how many cells will be inserted.
However, cells are inserted only into the same column as startCell.
If you want to insert whole rows, you could change it to this
tell application "Microsoft Excel"
set CellsToInsert to (get value of range "b1") - (get value of range "a1")
set InsertedCells to my InsertRows(get range "C3", CellsToInsert)
if not InsertedCells = false then
set value of InsertedCells to "XZ"
end if
end tell
on InsertRows(startCell, countOfInsertedRows)
tell application "Microsoft Excel"
try
set insertedRange to entire row of (get resize startCell row size countOfInsertedRows)
insert into range insertedRange shift shift down
return insertedRange
on error
return false
end try
end tell
end InsertRows
The key difference is using the Entire Row property of the Get Resize.
BTW, have you downloaded Microsoft’s Excel Apple Script reference?
http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7
Seeing as you just want a reference to the row(s) to select the entire row(s) in the line of code:
set insertedRange to entire row of (get resize startCell row size countOfInsertedRows)
then why use (get resize startCell row size countOfInsertedRows)
Why does
set insertedRange to entire row of (startCell)
not work? Or
set insertedRange to entire row of (get range of startCell)