|
List Box Columns
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 |
List Box Columns
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" 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. |
List Box Columns
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" wrote: 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" 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. |
List Box Columns
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,N10:P43,<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!N10:P43,<RelCol,0) ....or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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: 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" wrote: 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" 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. |
List Box Columns
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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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: 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" wrote: 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" 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. |
List Box Columns
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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. "Bob Bridges" wrote: 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. |
List Box Columns
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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. |
List Box Columns
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. "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. |
List Box Columns
Yes...mostly. I think I see an error he 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. --- "Mark" wrote: 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. --- "Bob Bridges" wrote: 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. |
List Box Columns
Bob,
Your solution works great! Excellent piece of work! I really appreciate your help and you're a real life saver. Thank you. Mark "Bob Bridges" wrote: Yes...mostly. I think I see an error he 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. --- "Mark" wrote: 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. --- "Bob Bridges" wrote: 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. |
List Box Columns
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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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. |
List Box Columns
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. |
List Box Columns
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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 |
List Box Columns
Thanks, but I guess I don't know what you mean by column identifiers.
"smartin" wrote: 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. |
List Box Columns
Mark wrote:
Thanks, but I guess I don't know what you mean by column identifiers. "smartin" wrote: 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! |
List Box Columns
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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 |
List Box Columns
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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 -- Dave Peterson |
List Box Columns
Dave,
That worked absolutely perfectly! Thank you so much for your help - I really appreciate it. Mark "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 -- Dave Peterson |
List Box Columns
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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 -- Dave Peterson |
List Box Columns
Untested!
You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 -- Dave Peterson -- Dave Peterson |
List Box Columns
Thanks, Dave, that cleared up a lot for me. Is there a way to set a range
within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 |
List Box Columns
You could open up the "sending" workbook to look at what's being used. But I'm
not sure that it's a good idea to be so specific. If you make changes to the Repository.xls workbook when the workbook that gets the formula is closed, then you could have trouble. Say your lookup range when you initially did the formula was A1:x23, but then you added a few more rows of info to that table -- A1:X35 (say). Your =vlookup() formulas won't adjust when you open the workbook with the old formulas. If I know that there's nothing under that table (and I like dedicating a single table per worksheet), I could use the whole column: A:X Not too dissimilar from your A1:X500, but just a bit more robust <bg. But if you want to open it and live dangerously: With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? 'I think you want this: set ScoresRng = rng.resize(, 24) 'resize with no rows specified means don't change that count of rows. End With I'm confused about what causes your #name error and even where the scoresrng is used. Mark wrote: Thanks, Dave, that cleared up a lot for me. Is there a way to set a range within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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. "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. 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,N10:P43,<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!N10:P43,<RelCol,0) ...or, if there's a space in the worksheet name, VLOOKUP(<Text,'Worksheet name'!N10:P43,<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'!N10:P43,<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 -- Dave Peterson |
List Box Columns
Thanks, Dave, but I feel like I'm already living dangerously tackling this
project. However, the number of students can change from the beginning to end of the school year and I didn't want to short the list . I like your idea of just using A:X, because the number of columns will always be fixed per grade - 1st graders only have 11 columns, so I'll use that solution. I haven't played with your solution, yet, but can the column be set to a variable depending upon the grade selected? Say if the User selects 3rd grade on the userform and there are 16 columns on sheet "3rd Grade" in the repository xls can the range be adjusted accordingly through a variable ............ If optBtn3 then grade ="3rd Grade" column = P end if Then in the vlookup formula set the range to 'A:column' Does that make any sense? "Dave Peterson" wrote: You could open up the "sending" workbook to look at what's being used. But I'm not sure that it's a good idea to be so specific. If you make changes to the Repository.xls workbook when the workbook that gets the formula is closed, then you could have trouble. Say your lookup range when you initially did the formula was A1:x23, but then you added a few more rows of info to that table -- A1:X35 (say). Your =vlookup() formulas won't adjust when you open the workbook with the old formulas. If I know that there's nothing under that table (and I like dedicating a single table per worksheet), I could use the whole column: A:X Not too dissimilar from your A1:X500, but just a bit more robust <bg. But if you want to open it and live dangerously: With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? 'I think you want this: set ScoresRng = rng.resize(, 24) 'resize with no rows specified means don't change that count of rows. End With I'm confused about what causes your #name error and even where the scoresrng is used. Mark wrote: Thanks, Dave, that cleared up a lot for me. Is there a way to set a range within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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 |
List Box Columns
You could, but I'm not sure I'd bother. Especially since you're converting to a
value right after inserting the formula. Dim myAddress as string myAddress = "A:" ..... if optbtn3 then grade = "3rd grade" myaddress = myaddress & "P" end if (I think???) Mark wrote: Thanks, Dave, but I feel like I'm already living dangerously tackling this project. However, the number of students can change from the beginning to end of the school year and I didn't want to short the list . I like your idea of just using A:X, because the number of columns will always be fixed per grade - 1st graders only have 11 columns, so I'll use that solution. I haven't played with your solution, yet, but can the column be set to a variable depending upon the grade selected? Say if the User selects 3rd grade on the userform and there are 16 columns on sheet "3rd Grade" in the repository xls can the range be adjusted accordingly through a variable ............ If optBtn3 then grade ="3rd Grade" column = P end if Then in the vlookup formula set the range to 'A:column' Does that make any sense? "Dave Peterson" wrote: You could open up the "sending" workbook to look at what's being used. But I'm not sure that it's a good idea to be so specific. If you make changes to the Repository.xls workbook when the workbook that gets the formula is closed, then you could have trouble. Say your lookup range when you initially did the formula was A1:x23, but then you added a few more rows of info to that table -- A1:X35 (say). Your =vlookup() formulas won't adjust when you open the workbook with the old formulas. If I know that there's nothing under that table (and I like dedicating a single table per worksheet), I could use the whole column: A:X Not too dissimilar from your A1:X500, but just a bit more robust <bg. But if you want to open it and live dangerously: With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? 'I think you want this: set ScoresRng = rng.resize(, 24) 'resize with no rows specified means don't change that count of rows. End With I'm confused about what causes your #name error and even where the scoresrng is used. Mark wrote: Thanks, Dave, that cleared up a lot for me. Is there a way to set a range within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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? "Bob Bridges" wrote: 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 -- Dave Peterson |
List Box Columns
Dave, I've taken all of your advice up to this point, so I don't think I will
bother with it. Thanks for all of your help, I really appreciate it. Mark "Dave Peterson" wrote: You could, but I'm not sure I'd bother. Especially since you're converting to a value right after inserting the formula. Dim myAddress as string myAddress = "A:" ..... if optbtn3 then grade = "3rd grade" myaddress = myaddress & "P" end if (I think???) Mark wrote: Thanks, Dave, but I feel like I'm already living dangerously tackling this project. However, the number of students can change from the beginning to end of the school year and I didn't want to short the list . I like your idea of just using A:X, because the number of columns will always be fixed per grade - 1st graders only have 11 columns, so I'll use that solution. I haven't played with your solution, yet, but can the column be set to a variable depending upon the grade selected? Say if the User selects 3rd grade on the userform and there are 16 columns on sheet "3rd Grade" in the repository xls can the range be adjusted accordingly through a variable ............ If optBtn3 then grade ="3rd Grade" column = P end if Then in the vlookup formula set the range to 'A:column' Does that make any sense? "Dave Peterson" wrote: You could open up the "sending" workbook to look at what's being used. But I'm not sure that it's a good idea to be so specific. If you make changes to the Repository.xls workbook when the workbook that gets the formula is closed, then you could have trouble. Say your lookup range when you initially did the formula was A1:x23, but then you added a few more rows of info to that table -- A1:X35 (say). Your =vlookup() formulas won't adjust when you open the workbook with the old formulas. If I know that there's nothing under that table (and I like dedicating a single table per worksheet), I could use the whole column: A:X Not too dissimilar from your A1:X500, but just a bit more robust <bg. But if you want to open it and live dangerously: With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? 'I think you want this: set ScoresRng = rng.resize(, 24) 'resize with no rows specified means don't change that count of rows. End With I'm confused about what causes your #name error and even where the scoresrng is used. Mark wrote: Thanks, Dave, that cleared up a lot for me. Is there a way to set a range within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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 |
List Box Columns
You may be in more trouble than you realize!!!
Good luck with the rest of your project. Mark wrote: Dave, I've taken all of your advice up to this point, so I don't think I will bother with it. Thanks for all of your help, I really appreciate it. Mark "Dave Peterson" wrote: You could, but I'm not sure I'd bother. Especially since you're converting to a value right after inserting the formula. Dim myAddress as string myAddress = "A:" ..... if optbtn3 then grade = "3rd grade" myaddress = myaddress & "P" end if (I think???) Mark wrote: Thanks, Dave, but I feel like I'm already living dangerously tackling this project. However, the number of students can change from the beginning to end of the school year and I didn't want to short the list . I like your idea of just using A:X, because the number of columns will always be fixed per grade - 1st graders only have 11 columns, so I'll use that solution. I haven't played with your solution, yet, but can the column be set to a variable depending upon the grade selected? Say if the User selects 3rd grade on the userform and there are 16 columns on sheet "3rd Grade" in the repository xls can the range be adjusted accordingly through a variable ............ If optBtn3 then grade ="3rd Grade" column = P end if Then in the vlookup formula set the range to 'A:column' Does that make any sense? "Dave Peterson" wrote: You could open up the "sending" workbook to look at what's being used. But I'm not sure that it's a good idea to be so specific. If you make changes to the Repository.xls workbook when the workbook that gets the formula is closed, then you could have trouble. Say your lookup range when you initially did the formula was A1:x23, but then you added a few more rows of info to that table -- A1:X35 (say). Your =vlookup() formulas won't adjust when you open the workbook with the old formulas. If I know that there's nothing under that table (and I like dedicating a single table per worksheet), I could use the whole column: A:X Not too dissimilar from your A1:X500, but just a bit more robust <bg. But if you want to open it and live dangerously: With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? 'I think you want this: set ScoresRng = rng.resize(, 24) 'resize with no rows specified means don't change that count of rows. End With I'm confused about what causes your #name error and even where the scoresrng is used. Mark wrote: Thanks, Dave, that cleared up a lot for me. Is there a way to set a range within the VLOOKUP formula to keep me out of trouble? I know I can apparently use $A$1:$X$500 without causing any problems, but I would like have it work like populating the combo box. I've tried setting the score range the same, but it fills the remaining cells with #NAME. Below is the Userform code as it stands now and it does work correctly: Private Sub UserForm_Activate() Dim wkbk As Workbook Dim Rng As Range Dim scores As Range Application.ScreenUpdating = False Set wkbk = Workbooks.Open(FileName:=studentFile & myWkbkName) With wkbk.Worksheets(grade) Set Rng = Intersect(.Range("A:A").EntireColumn, .UsedRange) 'Can I set the "scores" range here? End With RosterForm.ListBox1.ColumnCount = Rng.Rows.Count RosterForm.ListBox1.List = Rng.Value wkbk.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Private Sub CommandButton1_Click() Dim RowNdx As Long Dim ColNdx As Integer Dim SaveColNdx As Integer Dim wkbk As Workbook Application.ScreenUpdating = False ColNdx = 1 RowNdx = 13 For X = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(X) = True Then Cells(RowNdx + 1, 1).EntireRow.Insert Rows(RowNdx).Copy Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats Application.CutCopyMode = False Cells(RowNdx, 1).Value = ListBox1.List(X) For t = 2 To ActiveSheet.UsedRange.Columns.Count With Cells(RowNdx, t) .Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & studentPath & "[" & myWkbkName & "]" & grade & "'!$A$1:$X$500," & t & ", 0)" .Value = .Value End With Next t RowNdx = RowNdx + 1 End If Next X Rows(RowNdx).Delete Shift:=xlUp Unload Me Application.ScreenUpdating = True End Sub The string "grade" is set based upon the User's selection prior to populating the combo box. "Dave Peterson" wrote: Untested! You'll want to split that studentfile string into two pieces--on with the path and one with the filename. You could use code or just make a couple of constants: 'include the trailing backslash Constant myPath as string = "C:\documents and settings\HP_Owner\my documents\" Constant myWkbkName as string = "Repository.xls" constant myWorksheetName as string = "Grade 4" constant myAddress as string = "$a$1:$x$23" dim StudentFile as string studentfile = mypath & mywkbkname ..formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _ & ",'" & myPath _ & "[" & myWkbkName & "]" _ & myworksheetname & "'!" _ & myAddress & "," & t & ", 0)" ========= If that repository.xls workbook is open, then you could get the usedrange. But if that workbook is closed, then you won't be able to. Mark wrote: 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 "Dave Peterson" wrote: 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. Mark wrote: 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". "Dave Peterson" wrote: 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 "Bob Bridges" wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 10:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com