Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
I asked about this problem earlier and got a response(see
below for code and request for help) but I cannot get it to work. I get an error msg saying "For Each can only iterate over a collection object or an array" Can anyone suggest a way of fixing this? Thank you James ---------------------------------------------------------- Should be fairly simple. 1. Get the selected ITEM from the listbox. 2. Search through all items in the first column for the value (assuming column 1 starting with row 1). 3. When found, set the text boxes to the values in columns F and G (6 & 7) for the row it was found and then exit. ----------------------------------------------------------- Sub GetDetails_Click() Dim r as Range Dim strFind as String strFind = SiteSelected.Text For Each r in Range("C2:C" & Range("A65535").End (xlUp).Row If r.Value = strFind Then ProjNo.Text = Range("AS" & r.row).Value ProjTitle.Text = Range("AT" & r.row).Value ProjDate.Text = Range("AU" & r.row).value ProjLat.Text = Range("AP" & r.row).Value ProjLong.Text = Range("AQ" & r.row).Value Exit For End If Next End Sub ----------------------------------------------------------- I have a large spreadsheet with lots of columns. I have designed a UserForm to Help me select information to look at. I have categorized the items and can fill list & text boxes with "stage one" data. There are no two entries the same. What I want to do is push a button and populate some list boxes with "stage two" data. I think that this involves a search in the first column to find the item and then some method to bring in the data from the same row but several columns to the right. ITEM COL2 COL3 COL4 COL5 INFO1 INFO2 Seach for a specific item then fill box 1 and 2 with INFO1 and INFO2 respectively. I have spent 5hours today trying to do this and failed miserably. Can anyone help??? Thanks James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
James, looks like the code is missing a ")" after the
(xlUp).Row Rgds Rog -----Original Message----- I asked about this problem earlier and got a response(see below for code and request for help) but I cannot get it to work. I get an error msg saying "For Each can only iterate over a collection object or an array" Can anyone suggest a way of fixing this? Thank you James ---------------------------------------------------------- Should be fairly simple. 1. Get the selected ITEM from the listbox. 2. Search through all items in the first column for the value (assuming column 1 starting with row 1). 3. When found, set the text boxes to the values in columns F and G (6 & 7) for the row it was found and then exit. ---------------------------------------------------------- - Sub GetDetails_Click() Dim r as Range Dim strFind as String strFind = SiteSelected.Text For Each r in Range("C2:C" & Range("A65535").End (xlUp).Row If r.Value = strFind Then ProjNo.Text = Range("AS" & r.row).Value ProjTitle.Text = Range("AT" & r.row).Value ProjDate.Text = Range("AU" & r.row).value ProjLat.Text = Range("AP" & r.row).Value ProjLong.Text = Range("AQ" & r.row).Value Exit For End If Next End Sub ---------------------------------------------------------- - I have a large spreadsheet with lots of columns. I have designed a UserForm to Help me select information to look at. I have categorized the items and can fill list & text boxes with "stage one" data. There are no two entries the same. What I want to do is push a button and populate some list boxes with "stage two" data. I think that this involves a search in the first column to find the item and then some method to bring in the data from the same row but several columns to the right. ITEM COL2 COL3 COL4 COL5 INFO1 INFO2 Seach for a specific item then fill box 1 and 2 with INFO1 and INFO2 respectively. I have spent 5hours today trying to do this and failed miserably. Can anyone help??? Thanks James . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
Hello
Rog is right but I would also suggest you modify: For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row).Cells HTH Cordially Pascal "James Batley" a écrit dans le message de ... I asked about this problem earlier and got a response(see below for code and request for help) but I cannot get it to work. I get an error msg saying "For Each can only iterate over a collection object or an array" Can anyone suggest a way of fixing this? Thank you James ---------------------------------------------------------- Should be fairly simple. 1. Get the selected ITEM from the listbox. 2. Search through all items in the first column for the value (assuming column 1 starting with row 1). 3. When found, set the text boxes to the values in columns F and G (6 & 7) for the row it was found and then exit. ----------------------------------------------------------- Sub GetDetails_Click() Dim r as Range Dim strFind as String strFind = SiteSelected.Text For Each r in Range("C2:C" & Range("A65535").End (xlUp).Row If r.Value = strFind Then ProjNo.Text = Range("AS" & r.row).Value ProjTitle.Text = Range("AT" & r.row).Value ProjDate.Text = Range("AU" & r.row).value ProjLat.Text = Range("AP" & r.row).Value ProjLong.Text = Range("AQ" & r.row).Value Exit For End If Next End Sub ----------------------------------------------------------- I have a large spreadsheet with lots of columns. I have designed a UserForm to Help me select information to look at. I have categorized the items and can fill list & text boxes with "stage one" data. There are no two entries the same. What I want to do is push a button and populate some list boxes with "stage two" data. I think that this involves a search in the first column to find the item and then some method to bring in the data from the same row but several columns to the right. ITEM COL2 COL3 COL4 COL5 INFO1 INFO2 Seach for a specific item then fill box 1 and 2 with INFO1 and INFO2 respectively. I have spent 5hours today trying to do this and failed miserably. Can anyone help??? Thanks James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
Why - adding cells in this case doesn't do anything different.
-- Regards, Tom Ogilvy "papou" wrote in message ... Hello Rog is right but I would also suggest you modify: For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row).Cells HTH Cordially Pascal "James Batley" a écrit dans le message de ... I asked about this problem earlier and got a response(see below for code and request for help) but I cannot get it to work. I get an error msg saying "For Each can only iterate over a collection object or an array" Can anyone suggest a way of fixing this? Thank you James ---------------------------------------------------------- Should be fairly simple. 1. Get the selected ITEM from the listbox. 2. Search through all items in the first column for the value (assuming column 1 starting with row 1). 3. When found, set the text boxes to the values in columns F and G (6 & 7) for the row it was found and then exit. ----------------------------------------------------------- Sub GetDetails_Click() Dim r as Range Dim strFind as String strFind = SiteSelected.Text For Each r in Range("C2:C" & Range("A65535").End (xlUp).Row If r.Value = strFind Then ProjNo.Text = Range("AS" & r.row).Value ProjTitle.Text = Range("AT" & r.row).Value ProjDate.Text = Range("AU" & r.row).value ProjLat.Text = Range("AP" & r.row).Value ProjLong.Text = Range("AQ" & r.row).Value Exit For End If Next End Sub ----------------------------------------------------------- I have a large spreadsheet with lots of columns. I have designed a UserForm to Help me select information to look at. I have categorized the items and can fill list & text boxes with "stage one" data. There are no two entries the same. What I want to do is push a button and populate some list boxes with "stage two" data. I think that this involves a search in the first column to find the item and then some method to bring in the data from the same row but several columns to the right. ITEM COL2 COL3 COL4 COL5 INFO1 INFO2 Seach for a specific item then fill box 1 and 2 with INFO1 and INFO2 respectively. I have spent 5hours today trying to do this and failed miserably. Can anyone help??? Thanks James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
What I want to do is push a button and populate some list
boxes with "stage two" data. What about that statement James. Was that a Joke? I gave you code that would do that. (and it fixed the problem you are having now). -- Regards, Tom Ogilvy "James Batley" wrote in message ... I asked about this problem earlier and got a response(see below for code and request for help) but I cannot get it to work. I get an error msg saying "For Each can only iterate over a collection object or an array" Can anyone suggest a way of fixing this? Thank you James ---------------------------------------------------------- Should be fairly simple. 1. Get the selected ITEM from the listbox. 2. Search through all items in the first column for the value (assuming column 1 starting with row 1). 3. When found, set the text boxes to the values in columns F and G (6 & 7) for the row it was found and then exit. ----------------------------------------------------------- Sub GetDetails_Click() Dim r as Range Dim strFind as String strFind = SiteSelected.Text For Each r in Range("C2:C" & Range("A65535").End (xlUp).Row If r.Value = strFind Then ProjNo.Text = Range("AS" & r.row).Value ProjTitle.Text = Range("AT" & r.row).Value ProjDate.Text = Range("AU" & r.row).value ProjLat.Text = Range("AP" & r.row).Value ProjLong.Text = Range("AQ" & r.row).Value Exit For End If Next End Sub ----------------------------------------------------------- I have a large spreadsheet with lots of columns. I have designed a UserForm to Help me select information to look at. I have categorized the items and can fill list & text boxes with "stage one" data. There are no two entries the same. What I want to do is push a button and populate some list boxes with "stage two" data. I think that this involves a search in the first column to find the item and then some method to bring in the data from the same row but several columns to the right. ITEM COL2 COL3 COL4 COL5 INFO1 INFO2 Seach for a specific item then fill box 1 and 2 with INFO1 and INFO2 respectively. I have spent 5hours today trying to do this and failed miserably. Can anyone help??? Thanks James |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
To make the intent less ambiguous and to promote coding practices that
save trouble when it matters. Thought not functionally critical "in this case," I think it is a reasonable suggestion. I have seen numerous cases where folks (professionals) have inadvertently used the default value of an object or implicit type casting when they meant something else. Many who post questions on this group don't even know about default values, which, as far as I know, are an idiosyncrasy only of VB. Tom Ogilvy wrote: Why - adding cells in this case doesn't do anything different. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
Value is the default property of a range. Not sure where returning a range
object has anything to do with default values. In the usage shown, Cells would have no effect. The place where it would be needed is if Columns was appended to end of the statement: ? Range("C2:C" & Range("A65535").End(xlUp).Row).Count 2 ? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count 1 ? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count 2 But appending cells to the original has no affect: ? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count 2 -- Regards, Tom Ogilvy "Bob Kilmer" wrote in message ... To make the intent less ambiguous and to promote coding practices that save trouble when it matters. Thought not functionally critical "in this case," I think it is a reasonable suggestion. I have seen numerous cases where folks (professionals) have inadvertently used the default value of an object or implicit type casting when they meant something else. Many who post questions on this group don't even know about default values, which, as far as I know, are an idiosyncrasy only of VB. Tom Ogilvy wrote: Why - adding cells in this case doesn't do anything different. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
Value is the default property of a range.
Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row) If r.Value = strFind Then ... Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. Adding Cells doesn't make any difference because the expression returns a single cell Range into r anyway, which the code uses as such. I find that under Cells Property in Excel help it is says "the Item property is the default property for the Range object" which, in the case of a Range object, "returns a Range object". Clearly, Range(x) behaves different ways under different circumstances as VBA guesses at what is meant by what is written. I'd say at best, using the Range property without a qualifier is context dependent. Reasonably dependable, but context dependent, and therefore ambiguous. Range("B1:B10") = 5 'assigns 5 as the Value of each cell ? Range("B1:B10") 'doesn't work at all - type mismatch ? Typename(Range("B1:B10")) 'returns Range Sub Main() Dim r 'Prints "Range" For Each r In Range("A1:A10") Debug.Print CStr(TypeName(r)) Next r 'Prints "Range" For Each r In Range("A1:A10").Cells Debug.Print CStr(TypeName(r)) Next r 'Prints "Double," or "String," or ... For Each r In Range("A1:A10").Value Debug.Print CStr(TypeName(r)) Next r End Sub I try to be explicit and would tend to use Cells under the present circumstances because I believe that is what is being returned into r and my intent would be to use r as such. Regards, Bob Tom Ogilvy wrote: Value is the default property of a range. Not sure where returning a range object has anything to do with default values. In the usage shown, Cells would have no effect. The place where it would be needed is if Columns was appended to end of the statement: ? Range("C2:C" & Range("A65535").End(xlUp).Row).Count 2 ? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count 1 ? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count 2 But appending cells to the original has no affect: ? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count 2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
Value is the default property of a range.
Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. What code are you looking at Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range r is dimmed as Range and the control variable should and will be a range Cells(row, column) - not sure how that would be the default property of a range even if the two arguments can be left off. you couldn't do sStr = r unless value was the default. However, in the code in question r .Value is specified - there is no implicit or default assignment. In your little demo program. As you see in the first two cases. there is no default value invoked because in this context a range is a range which is what we are looping through. And as you demonstrate, adding cells accomplishes nothing. (my whole original comment in this thread) In your third loop, you are looping through and array, so whatever the content of the array is will be what type returns. Just one more time. in the constuct for each r in Range("C2:C" & Range("A65535").End(xlUp).Row) Range("C2:C" & Range("A65535").End(xlUp).Row) is interpreted as a range object - there is no default value - it is a range that is needed and a range that is provided. -- Regards, Tom Ogilvy "Bob Kilmer" wrote in message ... Value is the default property of a range. Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row) If r.Value = strFind Then ... Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. Adding Cells doesn't make any difference because the expression returns a single cell Range into r anyway, which the code uses as such. I find that under Cells Property in Excel help it is says "the Item property is the default property for the Range object" which, in the case of a Range object, "returns a Range object". Clearly, Range(x) behaves different ways under different circumstances as VBA guesses at what is meant by what is written. I'd say at best, using the Range property without a qualifier is context dependent. Reasonably dependable, but context dependent, and therefore ambiguous. Range("B1:B10") = 5 'assigns 5 as the Value of each cell ? Range("B1:B10") 'doesn't work at all - type mismatch ? Typename(Range("B1:B10")) 'returns Range Sub Main() Dim r 'Prints "Range" For Each r In Range("A1:A10") Debug.Print CStr(TypeName(r)) Next r 'Prints "Range" For Each r In Range("A1:A10").Cells Debug.Print CStr(TypeName(r)) Next r 'Prints "Double," or "String," or ... For Each r In Range("A1:A10").Value Debug.Print CStr(TypeName(r)) Next r End Sub I try to be explicit and would tend to use Cells under the present circumstances because I believe that is what is being returned into r and my intent would be to use r as such. Regards, Bob Tom Ogilvy wrote: Value is the default property of a range. Not sure where returning a range object has anything to do with default values. In the usage shown, Cells would have no effect. The place where it would be needed is if Columns was appended to end of the statement: ? Range("C2:C" & Range("A65535").End(xlUp).Row).Count 2 ? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count 1 ? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count 2 But appending cells to the original has no affect: ? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count 2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
"Tom Ogilvy" wrote in message
... Value is the default property of a range. Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. This 'moreover" comment was misconceived. I was thinking it supported a "value is not default property of a range" argument, but it does not. What code are you looking at Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range r is dimmed as Range and the control variable should and will be a range So far so good. Cells(row, column) - not sure how that would be the default property of a range even if the two arguments can be left off. Help says Item is the default property of a range, which returns a Range, but this is an untidy digression. The suggestion by an earlier poster that I supported was to qualify the Range by appending the Cells property. I say its merit is to make the statement Range("C2:C" & Range("A65535").End(xlUp).Row) unambiguous. "I mean the cells in this range, not the columns or the rows which are also part of this range." Not being explicit leaves it up to the reader/maintenance programmer to interpret which of the alternatives is meant and lets VBA decide which meaning applies. you couldn't do sStr = r unless value was the default. What is the value of the range Range("A1:B2")? Can you assign this value to any non-variant? If Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range and Value is the default property of a range, why does r end up containing a range, and a single cell range at that, rather than a value? However, in the code in question r .Value is specified - there is no implicit or default assignment. The code in question for me is Range("C2:C" & Range("A65535").End(xlUp).Row). The question is "Is it a good idea to qualify it with Cells or not?" In your little demo program. As you see in the first two cases. there is no default value invoked because in this context a range is a range which is what we are looping through. I am suggesting that unless you ask for something else, you get a single cell range, which makes a single cell range the default under the circumstances. One could ask for Columns or Rows or Values. And as you demonstrate, adding cells accomplishes nothing. (my whole original comment in this thread) .... except that it says to everyone that, "I mean the cells in this range and not rows or columns!" which is my original point. Truely, this is a pretty trivial example, but make it a little more complicated. Suppose it were a larger, mulit-dimensional range in a significant piece of code and you meant to code Range(blahblah).Columns but unintentionally left off Columns. The code might well run, but some maintenance programmer down the road trying to figure out what the code was doing - or was meant to have been doing - absent copious comments - might not find it obvious whether you meant Cells, Rows, or Columns. In your third loop, you are looping through an array, so whatever the content of the array is will be what type returns. Array? I am explicit requesting Values from the iterator of the collection. Just one more time. in the constuct for each r in Range("C2:C" & Range("A65535").End(xlUp).Row) Range("C2:C" & Range("A65535").End(xlUp).Row) is interpreted as a range object - there is no default value - it is a range that is needed and a range that is provided. Or, a collection of cells (not columns or rows) is needed and the default behavior of a multi-cell range in this context is that of a collection of cells and we may as well be explicit about it. -- Regards, Tom Ogilvy Truely, regards to you as well, Tom. Bob "Bob Kilmer" wrote in message ... Value is the default property of a range. Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row) If r.Value = strFind Then ... Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. Adding Cells doesn't make any difference because the expression returns a single cell Range into r anyway, which the code uses as such. I find that under Cells Property in Excel help it is says "the Item property is the default property for the Range object" which, in the case of a Range object, "returns a Range object". Clearly, Range(x) behaves different ways under different circumstances as VBA guesses at what is meant by what is written. I'd say at best, using the Range property without a qualifier is context dependent. Reasonably dependable, but context dependent, and therefore ambiguous. Range("B1:B10") = 5 'assigns 5 as the Value of each cell ? Range("B1:B10") 'doesn't work at all - type mismatch ? Typename(Range("B1:B10")) 'returns Range Sub Main() Dim r 'Prints "Range" For Each r In Range("A1:A10") Debug.Print CStr(TypeName(r)) Next r 'Prints "Range" For Each r In Range("A1:A10").Cells Debug.Print CStr(TypeName(r)) Next r 'Prints "Double," or "String," or ... For Each r In Range("A1:A10").Value Debug.Print CStr(TypeName(r)) Next r End Sub I try to be explicit and would tend to use Cells under the present circumstances because I believe that is what is being returned into r and my intent would be to use r as such. Regards, Bob Tom Ogilvy wrote: Value is the default property of a range. Not sure where returning a range object has anything to do with default values. In the usage shown, Cells would have no effect. The place where it would be needed is if Columns was appended to end of the statement: ? Range("C2:C" & Range("A65535").End(xlUp).Row).Count 2 ? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count 1 ? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count 2 But appending cells to the original has no affect: ? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count 2 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms/Macro search
? typename(Range("A1:B9").Value)
Variant() You do what you want. -- Regards, Tom Ogilvy "Bob Kilmer" wrote in message ... "Tom Ogilvy" wrote in message ... Value is the default property of a range. Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. This 'moreover" comment was misconceived. I was thinking it supported a "value is not default property of a range" argument, but it does not. What code are you looking at Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range r is dimmed as Range and the control variable should and will be a range So far so good. Cells(row, column) - not sure how that would be the default property of a range even if the two arguments can be left off. Help says Item is the default property of a range, which returns a Range, but this is an untidy digression. The suggestion by an earlier poster that I supported was to qualify the Range by appending the Cells property. I say its merit is to make the statement Range("C2:C" & Range("A65535").End(xlUp).Row) unambiguous. "I mean the cells in this range, not the columns or the rows which are also part of this range." Not being explicit leaves it up to the reader/maintenance programmer to interpret which of the alternatives is meant and lets VBA decide which meaning applies. you couldn't do sStr = r unless value was the default. What is the value of the range Range("A1:B2")? Can you assign this value to any non-variant? If Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range and Value is the default property of a range, why does r end up containing a range, and a single cell range at that, rather than a value? However, in the code in question r .Value is specified - there is no implicit or default assignment. The code in question for me is Range("C2:C" & Range("A65535").End(xlUp).Row). The question is "Is it a good idea to qualify it with Cells or not?" In your little demo program. As you see in the first two cases. there is no default value invoked because in this context a range is a range which is what we are looping through. I am suggesting that unless you ask for something else, you get a single cell range, which makes a single cell range the default under the circumstances. One could ask for Columns or Rows or Values. And as you demonstrate, adding cells accomplishes nothing. (my whole original comment in this thread) ... except that it says to everyone that, "I mean the cells in this range and not rows or columns!" which is my original point. Truely, this is a pretty trivial example, but make it a little more complicated. Suppose it were a larger, mulit-dimensional range in a significant piece of code and you meant to code Range(blahblah).Columns but unintentionally left off Columns. The code might well run, but some maintenance programmer down the road trying to figure out what the code was doing - or was meant to have been doing - absent copious comments - might not find it obvious whether you meant Cells, Rows, or Columns. In your third loop, you are looping through an array, so whatever the content of the array is will be what type returns. Array? I am explicit requesting Values from the iterator of the collection. Just one more time. in the constuct for each r in Range("C2:C" & Range("A65535").End(xlUp).Row) Range("C2:C" & Range("A65535").End(xlUp).Row) is interpreted as a range object - there is no default value - it is a range that is needed and a range that is provided. Or, a collection of cells (not columns or rows) is needed and the default behavior of a multi-cell range in this context is that of a collection of cells and we may as well be explicit about it. -- Regards, Tom Ogilvy Truely, regards to you as well, Tom. Bob "Bob Kilmer" wrote in message ... Value is the default property of a range. Then in the For loop, you'd be meaning "for each value in the range" but you use r as if it were a Range object that has a Value. For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row) If r.Value = strFind Then ... Moreover, the control variable r needs to be a Variant or an Object. A Double won't do. Adding Cells doesn't make any difference because the expression returns a single cell Range into r anyway, which the code uses as such. I find that under Cells Property in Excel help it is says "the Item property is the default property for the Range object" which, in the case of a Range object, "returns a Range object". Clearly, Range(x) behaves different ways under different circumstances as VBA guesses at what is meant by what is written. I'd say at best, using the Range property without a qualifier is context dependent. Reasonably dependable, but context dependent, and therefore ambiguous. Range("B1:B10") = 5 'assigns 5 as the Value of each cell ? Range("B1:B10") 'doesn't work at all - type mismatch ? Typename(Range("B1:B10")) 'returns Range Sub Main() Dim r 'Prints "Range" For Each r In Range("A1:A10") Debug.Print CStr(TypeName(r)) Next r 'Prints "Range" For Each r In Range("A1:A10").Cells Debug.Print CStr(TypeName(r)) Next r 'Prints "Double," or "String," or ... For Each r In Range("A1:A10").Value Debug.Print CStr(TypeName(r)) Next r End Sub I try to be explicit and would tend to use Cells under the present circumstances because I believe that is what is being returned into r and my intent would be to use r as such. Regards, Bob Tom Ogilvy wrote: Value is the default property of a range. Not sure where returning a range object has anything to do with default values. In the usage shown, Cells would have no effect. The place where it would be needed is if Columns was appended to end of the statement: ? Range("C2:C" & Range("A65535").End(xlUp).Row).Count 2 ? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count 1 ? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count 2 But appending cells to the original has no affect: ? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro search | Excel Discussion (Misc queries) | |||
Search In a Macro/VBA | Excel Discussion (Misc queries) | |||
Userforms/Macro Search | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |