Excel conditional insert row

[slashdot]

Hey all,

I’m trying to do the following:

1. Insert 2 rows below the bottom value when the top value differs from the bottom value.

Script I have so far.

tell application "Microsoft Excel"
	activate
	set rowInsert to 2
	set startLoop to 1
	set endLoop to 21
	repeat with i from startLoop to endLoop
		set sourceCell to "B" & i as string
		set compareCell to "B" & (i + 1) as string
		set sourceValue to value of cell sourceCell of worksheet "Sheet1" of workbook "Workbook1"
		set compareValue to value of cell compareCell of worksheet "Sheet1" of workbook "Workbook1"
		
		if sourceValue ≠ compareValue then
			set cellShift to i & ":" & (i + 1) as string
			insert into range range cellShift shift shift down
			i + 2
		end if
	end repeat
end tell

Column b data

1
1
1
1
1
2
2
2
2
2
2
3
3
3
3

Desired Results:

1
1
1
1
1

2
2
2
2
2
2

3
3
3
3

I Know what the problem is :

		if sourceValue ≠ compareValue then
			set cellShift to i & ":" & (i + 1) as string
			insert into range range cellShift shift shift down
			i + 2
		end if

The repeat statement doesn’t increment by two. Instead it tries to continue the count. I tried to set a variable “counter” but it never increments. Always remains set to the initial number.

I’m not sure why I can’t define a another counter.

[kai]

Try something like this, slashdot:

tell application "Microsoft Excel"
	activate
	set startLoop to 1
	set endLoop to 21
	set c to 1 (* separate counter *)
	repeat with i from startLoop to endLoop
		set sourceCell to "B" & i
		set compareCell to "B" & (i + 1)
		tell worksheet "Sheet1" of workbook "Workbook1"
			set sourceValue to value of cell sourceCell
			set compareValue to value of cell compareCell
		end tell
		set c to c + 1 (* regular increment *)
		if sourceValue is not compareValue then
			set cellShift to (c as string) & ":" & (c + 1)
			insert into range range cellShift shift shift down
			set c to c + 2 (* additional increment *)
		end if
	end repeat
end tell

[slashdot]

Thanks for the direction kai.

Here’s the working code:

tell application "Microsoft Excel"
	activate
	set startLoop to 1
	set endLoop to 500
	set c to 1 (* separate counter *)
	repeat with i from startLoop to endLoop
		set c to c + 1 (* regular increment *)
		set sourceCell to "B" & c
		set compareCell to "B" & (c + 1)
		tell worksheet "Sheet1" of workbook "Workbook1"
			set sourceValue to value of cell sourceCell
			set compareValue to value of cell compareCell
		end tell
		if sourceValue ≠ "" and compareValue ≠ "" then
			c + 1 (*increments to next cell since current cell is empty*)
			if sourceValue ≠ compareValue then
				set cellShift to (c + 1) & ":" & (c + 2) as string
				insert into range range cellShift shift shift down
				set c to c + 2 (* additional increment *)
			end if
		end if
	end repeat
end tell

[slashdot]

How can you take the code above and make it so that the loop exits on the first occurrence of a blank cell and output that blank cell?

Cell Data Results
1 1 1
2 1 1
3 1 1
4 1 1
5 1 1
6 1 1
7 1 1
8 1 1
9 1 1
10 2
11 2
12 2 2
13 2 2
14 2 2
15 3 2
16 3 2
17 3
18 3
19 3
20 3
21 3
22 3
23

Results
exit on Cell 23
output Cell 23

tia

[kai]

Hi slashdot.

I assume (since the code won’t work correctly otherwise) that “Sheet1” of workbook “Workbook1” is also the active sheet. If that’s so, then we should be able to dispense with the sheet references altogether - perhaps something like this:

tell application "Microsoft Excel"
	set compareCell to "B1"
	if value of cell compareCell is not "" then
		set currRow to 1
		set screen updating to false (* usually executes faster *)
		repeat
			set sourceCell to "B" & currRow
			set compareCell to "B" & (currRow + 1)
			set sourceValue to value of cell sourceCell
			set compareValue to value of cell compareCell
			if compareValue is "" then exit repeat
			if sourceValue is not compareValue then
				set currRow to currRow + 2
				set cellShift to ((currRow - 1) as string) & ":" & currRow
				insert into range range cellShift shift shift down
			end if
			set currRow to currRow + 1
		end repeat
		set screen updating to true
	end if
	display dialog "First empty cell: " & compareCell
end tell