Mailing Lists: Apple Mailing Lists
 Image of Mac OS face in stamp
Re: Excel 2004 Scripting Block
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Excel 2004 Scripting Block

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
References: 
 >Excel 2004 Scripting Block (From: Jason Bourque <email@hidden>)



Visit the Apple Store online or at retail locations.
1-800-MY-APPLE

Contact Apple | Terms of Use | Privacy Policy

Copyright © 2011 Apple Inc. All rights reserved.