Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: functions

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    there is a really useful function that you can use in VBA called 'current region'.

    is there an equivalent in the actual worksheet functions of excel??

    Cheers

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jackeb...
    I could find nothing, however, if you will give some details as to what you are trying to accomplish, you might be surprised at the functions some of these guys come up with...
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Tom,

    I too canne find nowt, grrr

    basically i have been using the dynamic name ranges so loved by Oz who currently frequents this bord, the only problem is that the offset method is a little seams to get a little upset because my dynamic range is part of a query, when you refresh the queery the range only selects the refreshed bit, not the refreshed bit and adjacten cells, so I am looking for a really simple version, ie the current region method, to select the block that i would like to be my named range.
    any suggestions?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I am catching on here, could you not run your query in code and then utilize the current region property?
    Tom

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    tom there is one reason for this, I would like to stress it is not my doing and nothing i can do about it.... here is an example of the sql....

    SELECT Plant1_RDef.TagName, Plant1_RDef.Description, Plant1_AllReadings.DateTime, Plant1_AllReadings.pValue
    FROM `\EUDIMS07RMTA PMPACKAGEDatabaseGas Compression`.Plant1_AllReadings Plant1_AllReadings, `\EUDIMS07RMTA PMPACKAGEDatabaseGas Compression`.Plant1_RDef Plant1_RDef
    WHERE Plant1_RDef.RDefID = Plant1_AllReadings.RDefID AND ((Plant1_RDef.Description='Number Of Fired Starts (CNTR_01)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Hours Run (TIMR_01)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Gas Generator Speed (TNH_PR)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Ambient Temperature ITDP') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Inlet Temperature (CTIH)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Inlet Pressure (CIP)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Outlet Temperature (CTD)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='Compressor Outlet Pressure (CPD)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='PT Exhaust Temp (TTXC)') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='P3190 1st Stage Inlet Filter dp') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='P3191 2nd Stage Inlet Filter dp') AND (Plant1_RDef.MachineID=2) OR (Plant1_RDef.Description='PI3186 Comp Discharge Pressure') AND (Plant1_RDef.MachineID=2))
    ORDER BY Plant1_AllReadings.DateTime, Plant1_RDef.Description


    AAAAAAAAAAARrrrrrrrrrrggggggggggghhhhhhh

    I hate people that don't think into the future when developing databases......


    Ed

  6. #6
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 00:36, Jackeb wrote:
    there is a really useful function that you can use in VBA called 'current region'.

    is there an equivalent in the actual worksheet functions of excel??

    Cheers
    Hi Jackeb,
    Maybe I can help you,
    There is an icon to "Select Current Region", and you can put it on your toolbar (it looks like an X).
    You'll find it by Toolbars | Customize | Commands | Edit | select Current Region.
    Eli

    [ This Message was edited by: eliW on 2002-04-16 01:26 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I never knew that....

    the problem is that it returns the current region command in code rather than a worksheet command that i can use for the dynamic range in the name/define bit!?!?

    Ed

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I should be more specific,

    is there an alternative way of using the offset function?

    Cheers

    ED

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 00:36, Jackeb wrote:
    there is a really useful function that you can use in VBA called 'current region'.

    is there an equivalent in the actual worksheet functions of excel??

    Cheers
    Jackeb, 'Current Region' is implemented as an Excel command rather than a built-in Excel function. As previously stated it can be invoked using the 'Select Current Region' tool bar button, but it's origination is the Edit | Go To... | Special... set of selections.

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cheers,

    I think i have solved the problem now. I used the offset function and the count function and it worked in a dynamic range.

    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •