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

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

.

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

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

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.

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.

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

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