Using A Find Result in a Range

[LHeld]

Hello,

I'm trying to search for a word on a spreadsheet, in this case the word
"Label." I then want to copy everything from A1 to the row above that word
"Label" and paste it to a new spreadsheet. I've got everything worked out
except how to use the Find Result address in a new range. I'm sure it's
simple, so simple I can't find it in any tutorial. Currently I have to look
in each spreadsheet for the last row of the range I want to copy and type the
copy range manually into the code for each of the multiple spec sheet
spreadsheets I'm attempting to cut down into individual product spec sheets.
Perhaps somebody could point me in the right direction?

Thanks in advance to anybody.

My code, in case it helps:

activate application "Microsoft Excel"
repeat 10 times
tell application "Microsoft Excel"
set MainWkbk to active workbook
activate object worksheet "Sheet1"
set SpecSheetEnd to (find (range "1:100" of worksheet "Sheet1") what
"Label" after "1:1")
set SpecSheetRng to (get address SpecSheetEnd)
copy range range "A1:H88"
set newWkbk to make new workbook
tell newWkbk
activate object workbook newWkbk
tell sheet "Sheet1"
activate object worksheet "Sheet1" of newWkbk
set column width of range "A:A" of sheet "Sheet1" to 12 -- characters
(columns)
set column width of range "B:C" of sheet "Sheet1" to 4 -- characters
(columns)
set column width of range "D:E" of sheet "Sheet1" to 12 -- characters
(columns)
set column width of range "F:G" of sheet "Sheet1" to 4 -- characters
(columns)
set column width of range "H:H" of sheet "Sheet1" to 14 -- characters
(columns)
select sheet "Sheet1" of newWkbk
paste worksheet sheet "Sheet1" of newWkbk destination range "A1"
set StoryRow to (find (range "1:100" of worksheet "Sheet1") what "Label")
set row height of StoryRow to 35
set KeywordRow to (find (range "1:100" of worksheet "Sheet1") what "Key")
set row height of KeywordRow to 35
set IngredientRow to (find (range "1:100" of worksheet "Sheet1") what
"Ingredient")
set row height of IngredientRow to 35
set NutritionRow to (find (range "1:100" of worksheet "Sheet1") what
"Nutritional")
set row height of NutritionRow to 35
set WeightRow to (find (range "1:100" of worksheet "Sheet1") what
"Weight")
set row height of WeightRow to 35
set fc to (find (range "A:B" of worksheet "Sheet1") what "Weight")
make new horizontal page break at worksheet "Sheet1" of newWkbk ¬
with properties {location:range (get address fc)}
set fName to string value of range "D1" & " " & string value of range
"D3" & " " & string value of range "D4"
save workbook as newWkbk filename fName
tell newWkbk
activate object workbook MainWkbk
activate object worksheet "Sheet1" of MainWkbk
tell sheet "Sheet1"
close window 1
end tell
activate application "Microsoft Excel"
activate object workbook 1
tell workbook active workbook
tell sheet "Sheet1"
activate object worksheet "Sheet1" of active workbook
set SpecSheetEnd to (find (range "1:100" of worksheet "Sheet1") what
"Label" after "1:1")
set SpecSheetRng to "A1:H88"
delete range range SpecSheetRng shift shift up
end tell
end tell
end tell
end tell

end tell
end tell
end repeat

 

[John McGhie]

I would hate to tell you how long I floundered about before I figured this
out...

The Find result IS a range

An Excel Find returns either "nothing" or the range that matches what you
were looking for. Here's a VBA example:

With Worksheets(1).Range("a1:a500")
Set FindResult = .Find(2, lookin:=xlValues)
If Not FindResult Is Nothing Then
firstAddress = FindResult.Address
Do
FindResult.Value = 5
Set FindResult = .FindNext( FindResult)
Loop While Not FindResult Is Nothing And FindResult.Address <>
firstAddress
End If
End With

This is a very typical ³iterative Find² routine. Notice they conduct the
first Find outside the loop and return the result to the FindResult
variable.

Then they test FindResult to see if they found anything. If they didn¹t, it
will be ³nothing² and they exit. If FindResult is anything other than
³nothing² it¹s a range. They then extract the address of that range and
fall into a Do...Loop that keeps repeating until it either falls off the end
of the spreadsheet range or ends up back where it started.

That¹s a fairly convoluted way of writing this, by the way. I don¹t think
the person who wrote that was a VBA coder by training: it looks more like a
C++ structure to me. I would have used a ³While FindResult is not
nothing...² construct to save a couple of lines. But you get the picture.

So if your Find result comes back as anything but ³nothing² you¹re holding
the range you want in the palm of your hand, no need to do anything else

Hope this helps

Hello,

I'm trying to search for a word on a spreadsheet, in this case the word
"Label." I then want to copy everything from A1 to the row above that word
"Label" and paste it to a new spreadsheet. I've got everything worked out
except how to use the Find Result address in a new range. I'm sure it's
simple, so simple I can't find it in any tutorial. Currently I have to look
in each spreadsheet for the last row of the range I want to copy and type the
copy range manually into the code for each of the multiple spec sheet
spreadsheets I'm attempting to cut down into individual product spec sheets.
Perhaps somebody could point me in the right direction?

Thanks in advance to anybody.

My code, in case it helps:

activate application "Microsoft Excel"
repeat 10 times
tell application "Microsoft Excel"
set MainWkbk to active workbook
activate object worksheet "Sheet1"
set SpecSheetEnd to (find (range "1:100" of worksheet "Sheet1") what
"Label" after "1:1")
set SpecSheetRng to (get address SpecSheetEnd)
copy range range "A1:H88"
set newWkbk to make new workbook
tell newWkbk
activate object workbook newWkbk
tell sheet "Sheet1"
activate object worksheet "Sheet1" of newWkbk
set column width of range "A:A" of sheet "Sheet1" to 12 -- characters
(columns)
set column width of range "B:C" of sheet "Sheet1" to 4 -- characters
(columns)
set column width of range "D:E" of sheet "Sheet1" to 12 -- characters
(columns)
set column width of range "F:G" of sheet "Sheet1" to 4 -- characters
(columns)
set column width of range "H:H" of sheet "Sheet1" to 14 -- characters
(columns)
select sheet "Sheet1" of newWkbk
paste worksheet sheet "Sheet1" of newWkbk destination range "A1"
set StoryRow to (find (range "1:100" of worksheet "Sheet1") what "Label")
set row height of StoryRow to 35
set KeywordRow to (find (range "1:100" of worksheet "Sheet1") what "Key")
set row height of KeywordRow to 35
set IngredientRow to (find (range "1:100" of worksheet "Sheet1") what
"Ingredient")
set row height of IngredientRow to 35
set NutritionRow to (find (range "1:100" of worksheet "Sheet1") what
"Nutritional")
set row height of NutritionRow to 35
set WeightRow to (find (range "1:100" of worksheet "Sheet1") what
"Weight")
set row height of WeightRow to 35
set fc to (find (range "A:B" of worksheet "Sheet1") what "Weight")
make new horizontal page break at worksheet "Sheet1" of newWkbk ¬
with properties {location:range (get address fc)}
set fName to string value of range "D1" & " " & string value of range
"D3" & " " & string value of range "D4"
save workbook as newWkbk filename fName
tell newWkbk
activate object workbook MainWkbk
activate object worksheet "Sheet1" of MainWkbk
tell sheet "Sheet1"
close window 1
end tell
activate application "Microsoft Excel"
activate object workbook 1
tell workbook active workbook
tell sheet "Sheet1"
activate object worksheet "Sheet1" of active workbook
set SpecSheetEnd to (find (range "1:100" of worksheet "Sheet1") what
"Label" after "1:1")
set SpecSheetRng to "A1:H88"
delete range range SpecSheetRng shift shift up
end tell
end tell
end tell
end tell

end tell
end tell
end repeat

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[LHeld via MacKB.com]

Thanks for the reply! I'd actually worked that part out, that the find result
returns a range. What I can't do, then, is figure out how to create NewRange
out of it from A1:FindResultRange. I want to copy/paste/delete NewRange,
then do it all over again. I'm new to AppleScript completely, although I've
had coding experience in other systems, so I'm sure it's simply a matter of
knowing the right syntax...which I can't find. If you or anybody could help
me out, I'd appreciate an actual snippet of code showing how to do this. I
learns best from seeing stuff.

Thanks again.