Hi!
I’ve searched this forum for a solution on a problem I’m having when trying to script Excel 2008 Mac, but have found nothing that matches. I’m sure some of my findings could have been useful, but my knowledge of applescript is so far to limited.
What I try to do is this:
ï€ Find the row number of the first occurrance of “aText” and put the row number in the variable firstRow
ï€ Find the row number of the last occurence of “aText” and put the row number in the variable lastRow
ï€ Select the area (“A” & firstRow:“M” & lastRow)
ï€ Copy selection
This is part of a daily routine and it happens that “aText” does not exist at all.
Other days “aText” exists in only one row.
When there are more than one occurance of “aText” it is ALWAYS in consecutive rows.
The text I search for is ALWAYS in column M of the active sheet of the active workbook.
Can anybody give me a hint of how to achieve this?
tell application "Microsoft Excel"
set searchText to "aText" as text
set searchRange to get range "a:m" of active sheet
set oneFoundCell to get resize (get cells of row 1 of searchRange) row size 1 column size 1
set firstAddress to ""
set foundCells to {}
set oneFoundCell to (find searchRange what searchText after oneFoundCell look in values look at whole search order by columns search direction search next without match case)
set firstAddress to get address oneFoundCell
if firstAddress = "" then
return "no matching cells"
else
repeat until ((get address oneFoundCell) = firstAddress) and (0 < (count of foundCells))
copy oneFoundCell to end of foundCells
set oneFoundCell to find next searchRange after oneFoundCell
end repeat
end if
set address1 to (get address of oneFoundCell)
set address2 to range "m1"
set address3 to (get address of address2) & ":" & (address1)
set cellCount to (count every cell of range address3) --gets row number
set numberOfRows to (number of items in foundCells)
set range1 to range ("a" & cellCount)
set range2 to range ("m" & cellCount + numberOfRows - 1)
set range3 to (get address of range1) & ":" & (get address of range2)
set destRange to range "A20" of active sheet
copy range range range3 destination destRange
end tell
tell application "Microsoft Excel"
tell range "a:a"
set topRow to first row index of (find (column 1) what "aText" after (row (count of rows)) search direction search next)
set bottomrow to first row index of (find (column 1) what "aText" after range "A1" search direction search previous)
end tell
set rangeToCopy to get offset range ("A1:M1") row offset (topRow - 1)
set rangeToCopy to get resize rangeToCopy row size (bottomrow - topRow + 1)
copy range rangeToCopy
end tell