Find row with Text and Insert row before it using Applescript

Scripting Forums AppleScript | Mac OS X
#1

Hi All,

I am new MacScripter, I am no scripter or coder, but I have done basic stuff with Applescript

Every month-end, I have to work on an large data-set Excel file with 7 columns. The first column contains descriptive text, the other 6 contain numbers. It can have over 500 row at times.
I have to insert a row above certain text to separate sections of the data.

I would like to create a script, that will cycle through every 1st column of every row, find the text I am looking, select the row and insert a row above this row.

Since the number of text I have to look for varies, the script must be able to modify to search for any number of text. Eg, the least number of text I search for is around 20 but sometimes I may have up to 60 text to look for.

If I can get this script, this will save me major time, energy and headaches at month-end.

Anyone have any ideas how to go about this?

#2

Will there be only a single row that matches — so the search stops after the first match? Or could there be multiple matching rows?

And when you say that the text to match varies, how would you normally get the text? Is it on the clipboard? In a text file? In another sheet? Is it something you type in manually?

After inserting the row, should any data be entered in it?

#3

You’ll first want a script with a list property that contains the text your searching for.

You can either enter that manually in the script or have a user interface that ask for the list, or each individual text “add entry” button then an option for “done entries” button

You can then use this to check the cell.

You may need to perform the inserts after the fact since your enumerating all rows.

So you may need to have a list of rowsToInser

property listToCheck:{}
property rowsToInsert:{}

repeat with aRow in allRows

set testCell to aRow’s column 1

If listToCheck contains testCell (text value) then

add aRow to the end of rowsToInsert

end repeat

————-

repeat with aRow in rowsToInsert

“Insert new row above aRow”

end repeat

#4

Hi, thanks for replying,
I probably did a poor job explaining what I would like. Forgive me.
I will answer your questions separately.

Firstly, only the 1st Column of each row will be search, as it contain text.

“Will there be only a single row that matches” - Yes, the text in each row will be different, no duplicates or multiple matching rows at all. So a search will only have 1 match

so the search stops after the first match? - No, there are multiples searches, and each search is unique.

“And when you say that the text to match varies” - What I meant here is that I am doing multiple searches, the dataset contains a lot of different stuff. eg. I maybe looking for “Total Current Assets”, “Total Assets”, "Total Current Liabilities", “Total Liabilities”, “March”, “July”, “Germany”, “Ireland”, “Cocoa”, “Oil”, “Corn”. These are 12 searches but sometimes it can be 25 searches.

“After inserting the row, should any data be entered in it?” - No data is to be entered, just a blank row above that row where the text has been found.

I hope I did a better job explaining.

Thanks
Willis

#5

Hi, Thanks for your reply.

I have used your code, but I am getting an error when compiling at “add aRow to the end of rowsToInsert”

Applescript Compile Error
Expected end of line, etc, but found identifier

Here’s what I have so far, I only use one search text so far, until I get the code right. I had to change around some code as I got an error at compiling. It’s not complete.

use AppleScript version “2.4” – Yosemite (10.10) or later

use scripting additions

property listToCheck : {“Sales Growth”}

property rowsToInsert : {}

tell application “Microsoft Excel”

tell active sheet

set allRows to get every row of used range

end tell

repeat with aRow in allRows

set testCell to column 1 of aRow

if listToCheck contains (text value) of testCell then

add aRow to the end of rowsToInsert

end if

end repeat

repeat with aRow in rowsToInsert

“Insert new row above aRow”

end repeat

end tell

Place correct or modify where possible.

Kind Regards,
Willis

#6

This should do what you need. The list of search terms is set inside the script. If there is a file containing all the search terms, that could also be used. Assumes that there are zero blank lines in the data. Will process the front document in excel.

tell application "Microsoft Excel"
	tell workbook 1
		
		-- set findText to (input box prompt "Find this text: " type string) -- input single string
		set findList to {"delta", "jigsaw", "romeo"}
		
		with timeout of 10 seconds
			tell active sheet
				
				-- get initial working range
				set colA to intersect range1 range "A:A" range2 used range of it
				
				repeat with ft in findList
					
					set foundCell to find colA what ft --  search for text
					set foundRowNum to first row index of foundCell -- row of found text
					set foundRange to range ("A" & foundRowNum & ":G" & foundRowNum) -- corresponding range
					
					insert into range foundRange shift shift down -- insert blank row
					
					-- remap ranges after insertion	
					set bottomRange to range ("A" & (foundRowNum + 1)) -- get bottom region 
					set lri to first row index of (get end bottomRange direction toward the bottom) -- get last row index
					set colA to get resize colA row size (lri - 1) -- remap 
				end repeat
				
			end tell
		end timeout
	end tell
end tell

Below is the sample data that I used. You should be able to copy/paste into a spreadsheet.

descriptive bnums cnums dnums enums fnums gnums
alpha
beta
charlie
delta
echo
foxtrot
gamma
hedron
igloo
jigsaw
kilo
llama
mattress
necklace
oscar
papa
quebec
romeo
sierra
tango

Aside: Please edit your posts and put three back ticks alone on the line above and below your code, like so…

```
your code
```

Otherwise, it is difficult to read and to work with. Thanks.

#7

Hi Mockman

Yes, I got it to work perfectly with the sample data you supplied.
However, I got an error when I used it on my excel file, at the following line of code: (in italics)

set foundCell to find colA what ft – search for text

Applescript Error

Kinds regards
Willis

#8

Does it fail on the first time through or does it work at all?

Can you click on Script Editor’s ‘window’ menu and select ‘log history’. This should open a window that can help understand what was going on. If it’s not there or doesn’t open, look in the preferences for the History tab and check Log History.

Look for a line that resembles this and post it here.

find range "[reporto.xlsx]Sheet1!$A$1:$A$23" what "romeo"

Basically, this is supposed to search for the string in the list only in the specified range. Excel is saying that there is a parameter error which is likely the ‘range’ to search in or the ‘what’ to search for.

If the script did process any lines then try and find the last instance.

#9

Hi Mockman, your script is working flawlessly on my excel file. I do not know what caused that error previously, but I close my excel file, quit and restarted Excel and it worked. I did a couple tests with the 2 previous datasets and it worked as well, with no problem. It took under 4 secs to complete one file with 180 rows. This is going to save me so much time and effort.

Hooray, Hooray

Thanks so much for your help and expertise on my problem, I really, really, really appreciate it.

Kind Regards
Willis

1 Like