Excel Copy Range when cell value is 0

Scripting Forums AppleScript | Mac OS X
,
#1

Hi All,

I just started using applescript last week and I’m stuck and need some help. This is a simple script that currently moves a range of formulas (k5:s16) to the bottom of a table. This is what I have so far:

tell application “Microsoft Excel”
set lastFilledCell to (get end (range “b5:j1000” of worksheet “AD Orders”) direction toward the bottom)
set firstBlankCell to (get offset lastFilledCell row offset 1)
copy range (range “k5:s16”) destination firstBlankCell
end tell

The above script works but I now want the range of formulas (k5:s16) to be placed within the table starting on the first row where the first cell in column c is equal to 0. Note that the table runs from b5 to j1000. (Much of this is empty and the table will populate over time via another script I have).

Thank you in advance for any assistance you can provide.

#2

I know this may not help you much but you could definitely do this using a macro in Excel 2011 invoked by an Applescript.

#3

Unfortunately all the machines here are running Excel 2008. I’ve got PCs at home and can get VBA to do this but the code doesn’t seem to be compatible with the Excel 2008 macro feature. I’ll see if I can locate someone running 2011. Many thanks for the post!

#4

One possiblity would be to use Excel to create a dynamic Named Range.

Name: FirstZeroInC
RefersTo: =INDEX(Sheet1!$C:$C, MATCH(0, Sheet1!$C:$C, 0), 1)

Then you could use a script like

tell application "Microsoft Excel"
	set firstNewRow to first row index of range "FirstZeroInC"
	set formulaRange to range "K5:S16"
	set newRange to cell firstNewRow of range "K:K"
	set newRange to get resize newRange row size (count of rows of formulaRange) column size (count of columns of formulaRange)
	set formula r1c1 of newRange to get (formula r1c1 of formulaRange)
end tell
#5

Thank you for trying to help. I set up the dynamic named range, ran the script and received the following error when the script hit “first row index”:

Microsoft Excel got an error: The object you are trying to access does not exist

I’m going to try some variations of your script as I see how this should work.

#6

Is everything spelt the same?
Is the name entered correctly. What does putting =FirstZeroInC+2 in a cell return (it should be 2)

#7

Yes spelling was the cause of the error - sorry about that. When typing =FirstZeroInC+2 this does return a 2.

I re-ran the script. It did not copy the formulas (k5:s16) over to the bottom of the table (B:J), beginning in the row where c was = to 0. Instead the script copied the first row of formulas (k5:s5) down 6 rows to k6:s11. The formulas that were originally in rows k6:s11 then followed. The formulas that were in k12:s16 disappeared.

Thank you for trying!