Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
I am following http://www.anthony-vba.kefra.com/vba/vbabasic2.htm to
spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. If one does not supply arguments, and merely accesses the Cells function: Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
the syntac should fo been something like this
Set MyRange = Range("D5:H10") MyRange.Cells(1,2) = "XYZ" Which is putting data into E5 Cells(1,2) is refering to Row 1 Column 2 of the area D5:H10 " wrote: I am following http://www.anthony-vba.kefra.com/vba/vbabasic2.htm to spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. If one does not supply arguments, and merely accesses the Cells function: Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
OK, got it. That site was useful in that it indicated to me that
single-indexing can refer to cells beyond the range from which Cells was invoked. However, there is still a scenario in which I would like to understand whether there is a default range, and what that might be. If I use Cells without a "Range." qualifier, what is the assumed range? Thanks. On May 11, 11:59*am, Dave Peterson wrote: That syntax didn't work for me. But something like this would: ActiveSheet.Range("A1").Cells(1,1) Range("A1").Cells(1,1) But I'm not sure I would use this very often. Chip Pearson has some notes written by Alan Beban:http://www.cpearson.com/excel/cells.htm It may be an interesting read for you. wrote: I am followinghttp://www.anthony-vba.kefra.com/vba/vbabasic2.htmto spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. *Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. *If one does not supply arguments, and merely accesses the Cells function: * *Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? *If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
Thanks, Joel.
On May 11, 1:39*pm, joel wrote: the syntac should fo been something like this Set MyRange = Range("D5:H10") MyRange.Cells(1,2) = "XYZ" Which is putting data into E5 Cells(1,2) is refering to Row 1 Column 2 of the area D5:H10 " wrote: I am followinghttp://www.anthony-vba.kefra.com/vba/vbabasic2.htmto spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. *Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. *If one does not supply arguments, and merely accesses the Cells function: * *Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? *If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
If you use an unqualfied range object (either Range(...) or cells(...), then
it'll depend on where the code is located. If the code is in a General module, then Cells(2,26) will refer to Column 26 (Z) and row 2 of the activesheet. If the code is in a Worksheet module, then cells(2,26) will refer to Z2, but in the sheet that owns the code. Essentially, it's Range("a1").cells(2,26) But even though it's legal syntax-wise, it's not used very often. On the other hand, something like: Cells(x, 1).range("c1:e1") is a nice way to loop (varying X) through rows and doing something to the cells in column C:E of that row. Dim X as long with activesheet 'I'd recommend that you qualify all your ranges for x = 3 to 8 if .cells(x,"A").value = 3 then .cells(x,"A").range("c1:e1").clearcontents end if next x end with But there are lots of ways to do this same thing: Dim myCell as range dim myRng as range with activesheet set myrng = .range("a3:a8") 'or .range("a3").resize(6,1) end with for each mycell in myrng.cells if mycell.value = 3 then mycell.offset(0,2).resize(1,3).clearcontents end if next mycell ========== My guess is that you'll code in what you like or find most friendly, but you'll learn to read other's code so that you can either update it or learn from it. wrote: OK, got it. That site was useful in that it indicated to me that single-indexing can refer to cells beyond the range from which Cells was invoked. However, there is still a scenario in which I would like to understand whether there is a default range, and what that might be. If I use Cells without a "Range." qualifier, what is the assumed range? Thanks. On May 11, 11:59 am, Dave Peterson wrote: That syntax didn't work for me. But something like this would: ActiveSheet.Range("A1").Cells(1,1) Range("A1").Cells(1,1) But I'm not sure I would use this very often. Chip Pearson has some notes written by Alan Beban:http://www.cpearson.com/excel/cells.htm It may be an interesting read for you. wrote: I am followinghttp://www.anthony-vba.kefra.com/vba/vbabasic2.htmto spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. If one does not supply arguments, and merely accesses the Cells function: Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Range property
Dave, thanks for all the hand-holding you've provided. I'm going to
have to take your last post in two sessions, but it looks like good stuff. Just harkening back to my matlab days, I know there are many subleties that one can exploit to advantage. On May 11, 4:52*pm, Dave Peterson wrote: If you use an unqualfied range object (either Range(...) or cells(...), then it'll depend on where the code is located. If the code is in a General module, then Cells(2,26) will refer to Column 26 (Z) and row 2 of the activesheet. If the code is in a Worksheet module, then cells(2,26) will refer to Z2, but in the sheet that owns the code. Essentially, it's Range("a1").cells(2,26) But even though it's legal syntax-wise, it's not used very often. On the other hand, something like: Cells(x, 1).range("c1:e1") is a nice way to loop (varying X) through rows and doing something to the cells in column C:E of that row. Dim X as long with activesheet *'I'd recommend that you qualify all your ranges * for x = 3 to 8 * * if .cells(x,"A").value = 3 then * * * .cells(x,"A").range("c1:e1").clearcontents * * end if * next x end with But there are lots of ways to do this same thing: Dim myCell as range dim myRng as range with activesheet * set myrng = .range("a3:a8") 'or .range("a3").resize(6,1) end with for each mycell in myrng.cells * if mycell.value = 3 then * * *mycell.offset(0,2).resize(1,3).clearcontents * end if next mycell ========== My guess is that you'll code in what you like or find most friendly, but you'll learn to read other's code so that you can either update it or learn from it. wrote: OK, got it. *That site was useful in that it indicated to me that single-indexing can refer to cells beyond the range from which Cells was invoked. However, there is still a scenario in which I would like to understand whether there is a default range, and what that might be. *If I use Cells without a "Range." qualifier, what is the assumed range? Thanks. On May 11, 11:59 am, Dave Peterson wrote: That syntax didn't work for me. But something like this would: ActiveSheet.Range("A1").Cells(1,1) Range("A1").Cells(1,1) But I'm not sure I would use this very often. Chip Pearson has some notes written by Alan Beban:http://www.cpearson..com/excel/cells.htm It may be an interesting read for you. wrote: I am followinghttp://www.anthony-vba.kefra.com/vba/vbabasic2.htmto spin up on Excel/VBA programming. Consider a case in which the Range keyword in the right-hand-side of an assignment. *Most of the examples in this page show arguments supplied to Range to specify a matrix of cells on the spreadsheet. *If one does not supply arguments, and merely accesses the Cells function: * *Range.Cells(2,5) does that mean Range defaults to the entire spreadsheet? Alternatively, is there a concept of "current/active Range", which might (for example) be the last Range used in an expression? *If so, is this memory of the latest Range maintained on a worksheet-by- worksheet basis, or is there just one "current/active" Range? Do similar assumptions apply to a "current/active" WorkSheet? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range 's top property | Excel Programming | |||
Use of Range Property | Excel Programming | |||
VBA Range Property | Excel Programming | |||
range property of range object | Excel Programming | |||
Row and Column from Used Range property?? | Excel Programming |