Re: Excel 2004 Scripting Block
Re: Excel 2004 Scripting Block
- Subject: Re: Excel 2004 Scripting Block
- From: Paul Berkowitz <email@hidden>
- Date: Thu, 28 Oct 2004 11:29:36 -0700
Title: Re: Excel 2004 Scripting Block
On 10/28/04 6:11 AM, "Jason Bourque" <email@hidden> wrote:
I am trying to find a row that has a value in column D
The script runs but does nothing sometimes other it return a value of “”
This is some of the things I tried. Is there any reference on script 2004?
Whoa! You don't seem to know the terminology for specifying cells, Jason (officially the cell's "address"). Never has it been possible to specify something like 'cell "D" of row 3' - there's no such thing. There's no cell "D" of anything. Back in Excel X and earlier, the usual method was to use the "R1C1" terminology: cell "R3C4". That no longer works in Excel 2004. (The reason is that there were terrible bugs in localized versions of Excel: French, German, etc.) The correct way now is to refer to 'cell "D3"'. (This is known as A1 terminology.) If you don't specify of which range you mean (since it's relative: "A1" means the top left corner of the specified range, always), then it's taken to be for the sheet. The best method - that Excel's AppleScript always returns unless you ask for something else - is the "absolute" address of "$D$3": that means D3 as you see it on the sheet, no matter what ranges you may be dealing with. I'd recommend you always use the absolute method.
You can use indexes (indices) if you really want to: 'cell 4 or row 3 of active sheet' and 'cell 3 of column 4 of active sheet' will result in the identical cell "D3".
If you have a selected cell, you can discover its address by the command 'get address':
tell application "Microsoft Excel"
set s to selection
get address s
end tell
--> "$D$3"
Excel even knows that it's a single cell and gives the answer as above ("$D$3") rather than the usual full range reference ("$D$3:$D$3").
OK. Now, looking for the first cell of the used range whose value is "6001-014" crashes Excel here. This is a serious bug - I'll report it. 'whose' clauses don't seem to work on value of cells, without crashing. Nested repeat loops do work, although they're slow:
tell application "Microsoft Excel"
set ur to used range of active sheet
--set c to first cell of ur whose value = "6001-014" -- crashes
set c to ""
set exitRpt to false
repeat with i from 1 to (count rows of ur)
set theRow to row i of ur
repeat with j from 1 to (count cells of theRow)
set theCell to cell j of theRow
if value of theCell = "6001-014" then
set c to (get address theCell)
set exitRpt to true
exit repeat
end if
end repeat
if exitRpt then exit repeat
end repeat
return c
end tell
--> "$D$3"
There will be an Excel 2004 Reference in due course.
--
Paul Berkowitz
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Applescript-users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden