• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag
 

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Excel 2004 Scripting Block
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

  • Follow-Ups:
    • Re: Excel 2004 Scripting Block
      • From: Paul Berkowitz <email@hidden>
    • Re: Excel 2004 Scripting Block
      • From: Paul Berkowitz <email@hidden>
References: 
 >Excel 2004 Scripting Block (From: Jason Bourque <email@hidden>)

  • Prev by Date: Picture file to clipboard w/shell?
  • Next by Date: Re: text item delimiters
  • Previous by thread: Excel 2004 Scripting Block
  • Next by thread: Re: Excel 2004 Scripting Block
  • Index(es):
    • Date
    • Thread