Userforms/Macro search

[James Batley]

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

 

[Rog]

James, looks like the code is missing a ")" after the
(xlUp).Row

Rgds

Rog

 

[papou]

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

 

[Tom Ogilvy]

Why - adding cells in this case doesn't do anything different.

 

[Tom Ogilvy]

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).

 

[Bob Kilmer]

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]

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

 

[Bob Kilmer]

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]

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.

 

[Bob Kilmer]

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.


Truely, regards to you as well, Tom.
Bob

 

[Tom Ogilvy]

? typename(Range("A1:B9").Value)
Variant()

You do what you want.