functions

[Jackeb]

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

[Tom Schreiner]

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

[Jackeb]

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?

[Tom Schreiner]

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

[Jackeb]

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

[eliW]

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 ]

[Jackeb]

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

[Jackeb]

I should be more specific,

is there an alternative way of using the offset function?

Cheers

ED

[Mark W.]

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.

[Jackeb]

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