selecting range from different Worksheets ERROR

[Elie Abou Haydar]

Hello

im trying to select a range of cells from Sheet3 non active sheet.
below 2 codes are giving me error ofApplication-defined or object-defined erroreven if i use range(cells( same error.

''worksheets("Sheet3").Range("c3", Range("c2").End(xlDown)).Select

Sub SelectRangeDown()
With worksheets("Sheet3").Activate
Range("c3", Range("c2").End(xlDown)).Select
End With
End Sub

Appreciate your help,

thank you
regards

 

[DocAElstein]

From Help:


In a worksheet module, it refers to an range on that worksheet.

From Alan

....... - so "Application" is the sort of "happening" going on at the time and this "happening" here in my example only sort of saw the wks reference, so sort of stuck with it. ).....
Alan.

 

[shg]

....... - so "Application" is the sort of "happening" going on at the time and this "happening" here in my example only sort of saw the wks reference, so sort of stuck with it. ).....
Alan.

DE, I have no idea what that means.

 

[DocAElstein]

SH G

DE, I have no idea what that means.

I will do my best to explain...

VBA is , i think intended to appear as much as possible like a OOP language,

So-

In a Class module (Worksheet Thisworkbook Userform Chart ( Older ** Macro Module ) ), things becomes a Public member of that Class. I do not really understand that but I think the general idea is that it “belongs” there so things made / referenced in it are from there – hence your point about the Worksheets Unqualified range reference going to that Worksheet )

In a Normal Module, as Rory pointed out in the Link i gave, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.
It is not a Class Module, so the above Class stuff does not apply. I think you can think of it as a Code Module, maybe? - But maybe a ** newer VBA Code module so as not to confuse it with a earlier Macro Module thing ?!
So, anyway, we construct code in it!
( _ ...or i do try anyway to ?!? )
In constructing a typical code line you “Navigate” / go through / down the “Hierarchie.“ of the OOP chain .. ( But noting that as VBA is not really a true OOP you can “cheat” and go “up the Hierarchie.!?“
http://www.mrexcel.com/forum/excel-...workbooks-windows-oop-method.html#post3818458
.....)

VBA , we know , guesses often what you want, and at the start of many Code lines it adds something of the form
Application.
Or
Excel.Application
Or
Application.Excel
Or
( Non correct VBA OOP SomethingAfterApplication.SomethingElseAfterApplication.Application )
Etc. etc.
http://www.mrexcel.com/forum/excel-...workbooks-windows-oop-method.html#post3818347

VBA Knows you are in the Application of Excel, - or that is what it guesses in this case...I guesses

So :

_1 ) Effectively in a normal VBA Module you start, or somewhere along the lines you become, in the “Application”. That itself would usually be referring to the Active sheet. Then in any code line a “unqualified” “.Range” call will be referring to the Active sheet. Hence many people think that the “unqualified” “.Range” call in a normal module goes to the Active sheet. Usually it does. ( Just like usually VBA behaves like an OOP language ( and hence many people think it is ) )

_2) In the link i gave i did a code line which effectively took the Application to a Worksheet. Later “down”the code chain i did an unqualified .Range reference and it stayed there, referencing the Worksheet and not the Active Sheet.

_3) I have probably not got all the above quite right. I am just an ammeter, trying to learn. . But i think i am close. .........

Hope that helps.

Alan.de
(Germany)

 

[DocAElstein]

P.s. See also
http://www.mrexcel.com/forum/excel-...vs-[a1-a5]-benefits-dangers.html?#post4332606

_ . This is suggesting
_ a ) that somehow [] makes an unqualified range reference refer to the Application.Range as is the case in a normal module.
http://www.mrexcel.com/forum/excel-...-range-range-value-anomaly-2.html#post4038308

Range(“ “) in a Sheet Module attempts to find the Named Range in the Sheet of the sheet module in which the code is. This explains the first line which errors.
_ b ) For a Sheet Module, this line

Names.Add Name:="w", RefersTo:=ws.Range("A1")

Appears to set the “correct wanted” Range, but sets the “scope” to the sheet that the code is in. This somehow explains the second line which errors: - [] seems to ignore this “Scope” – It somehow “knows where to go.

( Note in passing if the Named range is set manually, then by default the Scope is set to the workbook ( as does the Names.Add by the code in a Normal module ) , in which case only Range("w").Value line errors for the case of the code in a different worksheet module, the “scope”, presumably in this case being valid for all sheets..(. somehow ) Or it is “ignored” )
_...........................................