List Box Columns

[Mark]

Hi,
I'm populating a list box from an Excel workbook with student's names and
each student can have as many as 25 columns of test scores stored on a
spreadsheet, within the workbook. The list box displays each student's name
only which the teacher will select the appropriate names and then populate a
new classroom roster in a new spreadsheet. My question is, how do I get the
available columns of test scores to populate in the adjoining columns of the
new classroom roster? The teacher needs to be able to see the test scores of
the incoming class to adjust the curriculum in preparation for the State
assessment test. Any help would be greatly appreciated. Thanks.
Mark

 

[Bob Bridges]

Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-down
list, some cell like A5 now has the student's name in it? And you want B5:Z5
to contain the 25 test scores from elsewhere in the workbook? And those test
scores are listed across from the student's name? So why isn't it just a
matter of a VLOOKUP in the worksheet itself? What am I missing?

 

[Mark]

Thanks, Bob.
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher opens
a seperate document based on a template that displays the list box and she
makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder than
it really is, so I'm open to any suggestions.
Mark

 

[Bob Bridges]

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what you
meant by "suppress the remaining columns", either. You'll have to rephrase
it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,0).
The <Text> is the student's name that you're looking up, the <Table> is the
table of student names and grades you're looking it up in, <RelCol> is the
column you want to retrieve once VLOOKUP has found the student's name and 0
means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

....or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename:
VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]Worksheet
name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Mark]

Bob,
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what you
meant by "suppress the remaining columns", either. You'll have to rephrase
it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,0).
The <Text> is the student's name that you're looking up, the <Table> is the
table of student names and grades you're looking it up in, <RelCol> is the
column you want to retrieve once VLOOKUP has found the student's name and 0
means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename:
VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]Worksheet
name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

Thanks, Bob.
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher opens
a separate document based on a template that displays the list box and she
makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder than
it really is, so I'm open to any suggestions.

 

[Bob Bridges]

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,0).
The <Text> is the student's name that you're looking up, the <Table> is the
table of student names and grades you're looking it up in, <RelCol> is the
column you want to retrieve once VLOOKUP has found the student's name and
0 means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher
opens a separate document based on a template that displays the list box
and she makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder
than it really is, so I'm open to any suggestions.

:
Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-
down list, some cell like A5 now has the student's name in it? And you
want B5:Z5 to contain the 25 test scores from elsewhere in the
workbook? And those test scores are listed across from the student's
name? So why isn't it just a matter of a VLOOKUP in the worksheet itself?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names
and each student can have as many as 25 columns of test scores stored
on a spreadsheet, within the workbook. The list box displays each
student's name only which the teacher will select the appropriate names
and then populate a new classroom roster in a new spreadsheet. My
question is, how do I get the available columns of test scores to
populate in the adjoining columns of the new classroom roster? The
teacher needs to be able to see the test scores of the incoming class to
adjust the curriculum in preparation for the State assessment test.

 

[Mark]

Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?
Mark

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires a
little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already populated
the list box and "My question is, how do I get the available columns of test
scores to populate in the adjoining columns of the new classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,0).
The <Text> is the student's name that you're looking up, the <Table> is the
table of student names and grades you're looking it up in, <RelCol> is the
column you want to retrieve once VLOOKUP has found the student's name and
0 means the table is not necessarily sorted on student name and you want an
exact match on the name, not just something close. (Of course I realize you
may not be doing the lookup on the student's name; you could be using student
number or something, but I figure you can adapt.) What we're looking at here
is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there. DON'T
HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the repository
workbook and select the cells that compose the table of student names and
their scores. Notice that in the formula bar, the partially-built formula is
still there and Excel is filling in the reference to these cells. Again, DO
NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the right
arguments in there. For example, if you filled in "A1" as I told you to, now
you may have to fill in the right reference for the student's name in this
row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

--- "Mark" wrote:
They are actually two different workbooks. Let's say a 5th grade teacher
wants to populate her current roster with students that were 4th graders,
last year. All 4th grade test scores from last year are kept in a workbook
called "4th grade repository" and the list box is populated with all of last
years 4th graders across the district (approximately 1100). The teacher
opens a separate document based on a template that displays the list box
and she makes her selections accordingly. I didn't know if it were possible to
populate the list box with all 1100 students via an array and suppress the
remaining columns or not, thus my question. I may be making this harder
than it really is, so I'm open to any suggestions.

:
Well, the list box ends up populating a cell somewhere in the Workbook,
right? I mean, once the teacher has selected a student from the drop-
down list, some cell like A5 now has the student's name in it? And you
want B5:Z5 to contain the 25 test scores from elsewhere in the
workbook? And those test scores are listed across from the student's
name? So why isn't it just a matter of a VLOOKUP in the worksheet itself?

--- "Mark" wrote:
I'm populating a list box from an Excel workbook with student's names
and each student can have as many as 25 columns of test scores stored
on a spreadsheet, within the workbook. The list box displays each
student's name only which the teacher will select the appropriate names
and then populate a new classroom roster in a new spreadsheet. My
question is, how do I get the available columns of test scores to
populate in the adjoining columns of the new classroom roster? The
teacher needs to be able to see the test scores of the incoming class to
adjust the curriculum in preparation for the State assessment test.

 

[Bob Bridges]

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
..UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

....then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Mark]

OK, I get it - I think - I just wasn't able to put it all together until your
last explanation. However, if I select A30 on a blank spreadsheet, put the
VLOOKUP (=VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,2,0) in each
remaining cell of the row, it only picks up the first column (B) and nothing
in the remaining columns. Thus, I can get the student and the first test
score, but that's it.

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Bob Bridges]

Yes...mostly. I think I see an error here: You say you can get only column
B from the repository, but that's easy to control from the third VLOOKUP
argument. In our example so far it's been a 2, but all that means is that
VLOOKUP will return column B from the table.

(Note: That's column B RELATIVE TO THE TABLE. If the students' names were
in column C, then the 2 in the third argument would pull column D; it just
means, you see, that it pulls the 2nd column from the left of the range
specified in the second argument.)

So one way to get every grade is to use VLOOKUP(A30,'[Repository.xls]Grade
4'!$A$1:$X$23,2,0) in column B, but VLOOKUP(A30,...,3,0) in column C,
=VLOOKUP(A30,...,4,0) in column D and so on. That'll get you every grade.

But for me that's too much work; I would probably require it to vary the
reference automatically, like this:

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.

OK, I get it - I think - I just wasn't able to put it all together until your
last explanation. However, if I select A30 on a blank spreadsheet, put the
VLOOKUP (=VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,2,0) in each
remaining cell of the row, it only picks up the first column (B) and nothing
in the remaining columns. Thus, I can get the student and the first test
score, but that's it.

That's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

...how do I retrieve the record of the test scores associated with the selected
name, if I don't retrieve the entire record initially?

--- "Mark" wrote:
Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and _
Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

--- "Bob Bridges" wrote:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just
requires a little additional information in the lookup-table argument to
tell Excel where to look for the table data.

....you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,
<RelCol>,0). The <Text> is the student's name that you're looking
up, the <Table> is the table of student names and grades you're
looking it up in, <RelCol> is the column you want to retrieve once
VLOOKUP has found the student's name and 0 means the table is not
necessarily sorted on student name and you want an exact match on
the name, not just something close. (Of course I realize you may not
be doing the lookup on the student's name; you could be using
student number or something, but I figure you can adapt.) What
we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to
specify the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you
have to specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path
and filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade
repository.xls]Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of
studentnames and their scores. Notice that in the formula bar, the
partially-built formula is still there and Excel is filling in the reference
to these cells. Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in
your VLOOKUP formula shows just the file name; after you close it,
it'll probably show the drive and path too. I have occasionally found
that this reference won't work unless the target table (the repository
in this case) is actually open in Excel; other times this is not the case.
I'm not sure why, but I think it has to do with the inaccessible
workbooks being on a shared drive, but I haven't pinned it down any
closer than that.

Now you have a working VLOOKUP formula, but you may not have all
the right arguments in there. For example, if you filled in "A1" as I
told you to, now you may have to fill in the right reference for the
student's name in this row. And I said to use 2 for the third
argument, but that's right only if the value you want to look up is in
the next column to the right of the student's name in the table. And
of course you'll have to copy the formula to all the other rows in the
roster, too.

 

[Mark]

Bob,
Your solution works great! Excellent piece of work! I really appreciate your
help and you're a real life saver. Thank you.
Mark

Yes...mostly. I think I see an error here: You say you can get only column
B from the repository, but that's easy to control from the third VLOOKUP
argument. In our example so far it's been a 2, but all that means is that
VLOOKUP will return column B from the table.

(Note: That's column B RELATIVE TO THE TABLE. If the students' names were
in column C, then the 2 in the third argument would pull column D; it just
means, you see, that it pulls the 2nd column from the left of the range
specified in the second argument.)

So one way to get every grade is to use VLOOKUP(A30,'[Repository.xls]Grade
4'!$A$1:$X$23,2,0) in column B, but VLOOKUP(A30,...,3,0) in column C,
=VLOOKUP(A30,...,4,0) in column D and so on. That'll get you every grade.

But for me that's too much work; I would probably require it to vary the
reference automatically, like this:

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.

OK, I get it - I think - I just wasn't able to put it all together until your
last explanation. However, if I select A30 on a blank spreadsheet, put the
VLOOKUP (=VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,2,0) in each
remaining cell of the row, it only picks up the first column (B) and nothing
in the remaining columns. Thus, I can get the student and the first test
score, but that's it.

That's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

--- "Mark" wrote:
...how do I retrieve the record of the test scores associated with the selected
name, if I don't retrieve the entire record initially?

--- "Mark" wrote:
Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and _
Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

--- "Bob Bridges" wrote:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just
requires a little additional information in the lookup-table argument to
tell Excel where to look for the table data.

....you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,
<RelCol>,0). The <Text> is the student's name that you're looking
up, the <Table> is the table of student names and grades you're
looking it up in, <RelCol> is the column you want to retrieve once
VLOOKUP has found the student's name and 0 means the table is not
necessarily sorted on student name and you want an exact match on
the name, not just something close. (Of course I realize you may not
be doing the lookup on the student's name; you could be using
student number or something, but I figure you can adapt.) What
we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to
specify the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you
have to specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path
and filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade
repository.xls]Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of
studentnames and their scores. Notice that in the formula bar, the
partially-built formula is still there and Excel is filling in the reference
to these cells. Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in
your VLOOKUP formula shows just the file name; after you close it,
it'll probably show the drive and path too. I have occasionally found
that this reference won't work unless the target table (the repository
in this case) is actually open in Excel; other times this is not the case.
I'm not sure why, but I think it has to do with the inaccessible
workbooks being on a shared drive, but I haven't pinned it down any
closer than that.

Now you have a working VLOOKUP formula, but you may not have all
the right arguments in there. For example, if you filled in "A1" as I
told you to, now you may have to fill in the right reference for the
student's name in this row. And I said to use 2 for the third
argument, but that's right only if the value you want to look up is in
the next column to the right of the student's name in the table. And
of course you'll have to copy the formula to all the other rows in the
roster, too.

 

[Mark]

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[smartin]

Bob Bridges wrote:

[snippens]

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.

One alternative is to place column identifiers in both worksheets, and
use INDEX/MATCH. This gets around using a relative column reference so
the formula will not break if a column is inserted somewhere.

It might look like this in cell Z30 (this is an array formula, complete
with Ctrl+Shift+Enter):

=INDEX('[Repository.xls]Grade 4'!$A$1:$X$23,
MATCH(1,--($A30='[Repository.xls]Grade 4'!$A$1:$A$23),0),
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0))

(line breaks entered for clarity)

This assumes row $1 has the column identifiers you want to coordinate.

 

[Dave Peterson]

If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.

Maybe you could plop the formula into the cell and then convert it to a value.

But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with

Untested--watch for typos.

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Mark]

Thanks, but I guess I don't know what you mean by column identifiers.

Bob Bridges wrote:

[snippens]

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.

One alternative is to place column identifiers in both worksheets, and
use INDEX/MATCH. This gets around using a relative column reference so
the formula will not break if a column is inserted somewhere.

It might look like this in cell Z30 (this is an array formula, complete
with Ctrl+Shift+Enter):

=INDEX('[Repository.xls]Grade 4'!$A$1:$X$23,
MATCH(1,--($A30='[Repository.xls]Grade 4'!$A$1:$A$23),0),
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0))

(line breaks entered for clarity)

This assumes row $1 has the column identifiers you want to coordinate.

 

[smartin]

Thanks, but I guess I don't know what you mean by column identifiers.

Bob Bridges wrote:

[snippens]

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.

One alternative is to place column identifiers in both worksheets, and
use INDEX/MATCH. This gets around using a relative column reference so
the formula will not break if a column is inserted somewhere.

It might look like this in cell Z30 (this is an array formula, complete
with Ctrl+Shift+Enter):

=INDEX('[Repository.xls]Grade 4'!$A$1:$X$23,
MATCH(1,--($A30='[Repository.xls]Grade 4'!$A$1:$A$23),0),
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0))

(line breaks entered for clarity)

This assumes row $1 has the column identifiers you want to coordinate.

Sorry I was not being totally clear.

In '[Repository.xls]Grade 4' suppose row 1 has labels (column
identifiers) like "Assignment 1", "Assignment 2", etc. This kind of
layout is typical.

For this example, in the worksheet looking up the values, place the
labels you want to look up in row 1. E.g., put "Assignment 1" in Z1.

Notice the 3rd parameter of the INDEX formula is
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0)

This basically says, "Get the value in row one and find it in the first
row of '[Repository.xls]Grade 4'. Use the column where we matched to
return the result."

You could also use VLOOKUP in this way as well. In the col_index_num
parameter, substitute the same MATCH.

Hope this helps!

 

[Mark]

Thanks, Dave. I did everything you said, and looked for typos, now I get a
"Run time error 1004 Applicatio defined or object defined error".

If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.

Maybe you could plop the formula into the cell and then convert it to a value.

But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with

Untested--watch for typos.

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

--- "Mark" wrote:
Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Dave Peterson]

I had the closing apostrophe in the wrong spot:

=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
.value = .value
end With

If that doesn't work, then change the formula so that it looks like a constant:

..formula = "$=vlookup(" & ....

Then the cell won't contain a formula--just that text. But you can manually
edit the formula (remove that leading $) and see what you can do to make it
right.

Post back with the formula that works.

Thanks, Dave. I did everything you said, and looked for typos, now I get a
"Run time error 1004 Applicatio defined or object defined error".

If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.

Maybe you could plop the formula into the cell and then convert it to a value.

But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with

Untested--watch for typos.

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

:

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

--- "Mark" wrote:
Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Mark]

Dave,
That worked absolutely perfectly! Thank you so much for your help - I really
appreciate it.
Mark

I had the closing apostrophe in the wrong spot:

=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
.value = .value
end With

If that doesn't work, then change the formula so that it looks like a constant:

..formula = "$=vlookup(" & ....

Then the cell won't contain a formula--just that text. But you can manually
edit the formula (remove that leading $) and see what you can do to make it
right.

Post back with the formula that works.

Thanks, Dave. I did everything you said, and looked for typos, now I get a
"Run time error 1004 Applicatio defined or object defined error".

If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.

Maybe you could plop the formula into the cell and then convert it to a value.

But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with

Untested--watch for typos.


Mark wrote:

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

:

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

--- "Mark" wrote:
Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.

 

[Mark]

I was wondering how, or if it's even possible, to substitute variables into
the VLOOKUP formula:

..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"

Specifically, the document path, the sheet name, and the range. I have the
document path declared as:
Public Const studentFile As String = "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls"

The sheet name as:
Public grade As String

And as far as the range, I thought:
Sheets(grade).UsedRange.address

I've tried to substitute these into the formula in various combinations, but
I can't seem to get anything to work. Does anyone have any ideas on this one?
Mark

I had the closing apostrophe in the wrong spot:

=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
.value = .value
end With

If that doesn't work, then change the formula so that it looks like a constant:

..formula = "$=vlookup(" & ....

Then the cell won't contain a formula--just that text. But you can manually
edit the formula (remove that leading $) and see what you can do to make it
right.

Post back with the formula that works.

Thanks, Dave. I did everything you said, and looked for typos, now I get a
"Run time error 1004 Applicatio defined or object defined error".

If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.

Maybe you could plop the formula into the cell and then convert it to a value.

But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)

with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with

Untested--watch for typos.


Mark wrote:

Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer

Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False

' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub

:

You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.

As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:

A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis

...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.

--- "Mark" wrote:
Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).

Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1

' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ListBox1.List(x)

Else
Unload Me
End If
Next x
End Sub

So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?

:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?

Or what? See, you've told me here what you intend, but so far I don't hear
a question....?

--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:

Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.

:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".

But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.

As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:

If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)

If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N1043,<RelCol>,0)

...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N1043,<RelCol>,0)

If it's in a different workbook entirely, you have to specify the path and
filename: VLOOKUP(<Text>,'S:\Path\More Path\[4th grade repository.xls]
Worksheet name'!N1043,<RelCol>,0)

Easier than trying to spell out all that and make sure you get all the
string delimiters in the right positions is to point Excel to the other
workbook and let it fill in the proper syntax. Like this:

1) Open the roster workbook.
2) Open the repository.
3) In a blank cell in the roster, type "=VLOOKUP(A1," and stop there.
DON'T HIT <Enter>, just continue to the next step.
4) Using mouse or <Ctl> keys, whichever you prefer, switch to the
repository workbook and select the cells that compose the table of student
names and their scores. Notice that in the formula bar, the partially-built
formula is still there and Excel is filling in the reference to these cells.
Again, DO NOT HIT <Enter>.
5) Finish the formula by typing ",2,0)". Now you may hit <Enter>.

While the repository workbook is open in Excel, the reference to it in your
VLOOKUP formula shows just the file name; after you close it, it'll probably
show the drive and path too. I have occasionally found that this reference
won't work unless the target table (the repository in this case) is actually
open in Excel; other times this is not the case. I'm not sure why, but I
think it has to do with the inaccessible workbooks being on a shared drive,
but I haven't pinned it down any closer than that.

Now you have a working VLOOKUP formula, but you may not have all the
right arguments in there. For example, if you filled in "A1" as I told you to,
now you may have to fill in the right reference for the student's name in
this row. And I said to use 2 for the third argument, but that's right only if
the value you want to look up is in the next column to the right of the
student's name in the table. And of course you'll have to copy the formula
to all the other rows in the roster, too.