D
Dave Peterson
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.
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.
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 said: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.
I was wondering how, or if it's even possible, to substitute variables into
the VLOOKUP formula:
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
Specifically, the document path, the sheet name, and the range. I have the
document path declared as:
Public Const studentFile As String = "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls"
The sheet name as:
Public grade As String
And as far as the range, I thought:
Sheets(grade).UsedRange.address
I've tried to substitute these into the formula in various combinations, but
I can't seem to get anything to work. Does anyone have any ideas on this one?
Mark
:
I had the closing apostrophe in the wrong spot:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\" _
& "[Repository.xls]Grade 4'!$A$1:$X$23," & t & ", 0)"
.value = .value
end With
If that doesn't work, then change the formula so that it looks like a constant:
..formula = "$=vlookup(" & ....
Then the cell won't contain a formula--just that text. But you can manually
edit the formula (remove that leading $) and see what you can do to make it
right.
Post back with the formula that works.
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".
:
If you want to use application.vlookup() in your code, then the workbook with
the table needs to be open.
Maybe you could plop the formula into the cell and then convert it to a value.
But you'll want to create the formula so that it looks like the formula you
would build by hand:
=VLOOKUP(A1,'C:\My Documents\Excel\[book1.xls]Sheet1'!$A:$B,2,FALSE)
with Cells(RowNdx, t)
.formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'C:\Documents and Settings\HP_Owner\My Documents\'" _
& "[Repository.xls]Grade 4!$A$1:$X$23," & t & ", 0)"
.value = .value
end with
Untested--watch for typos.
Mark wrote:
Bob,
I have a (quick, I hope) question on the VLOOKUP function. Apparently, I'm
supposed to maintain conditional formatting in the columns that hold the test
scores. Therefore, I attempted to convert everything to VBA which has been
causing me problems all day. In the code below, I get Error 2015 on the
designated line, which I think is tied to the VLOOKUP function. The columns
on the spreadsheet, aside from column A, fill with #VALUE!. Any ideas on this
one?
Mark
Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim LastRow As Integer
Application.ScreenUpdating = False
ColNdx = 1
RowNdx = 20
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1
' If the item is selected...
If ListBox1.Selected(x) = True Then
Cells(RowNdx + 1, 1).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
' display the Selected item.
Cells(RowNdx, 1).Value = ListBox1.List(x)
For t = 2 To 23
ERROR 2015 ==> Cells(RowNdx, t).Value = Application.VLookup(Cells(RowNdx,
ColNdx).Value, "C:\Documents and Settings\HP_Owner\My
Documents\Repository.xls Grade 4!$A$1:$X$23", t, 0)
Next t
RowNdx = RowNdx + 1
End If
Next x
Unload Me
Application.ScreenUpdating = True
End Sub
:
You mentioned the first issue before, but I just didn't get it. The list box
is showing more than just the student's name? ...Ok, I see (now that I look
more closely) that you're populating the list box with an intersection of
.UsedRange and columns A through Z. But why? I must repeat that I've never
used a list box in Excel, but it seems to me that if the student's name is in
column B, then you should use Intersect(.Range("B"), .UsedRange) instead.
Again, that looks too obvious so it's possible you just overlooked the
obvious (we all do that) but it's also possible I'm missing something.
As for the second question ("how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially?"), that's why I keep talking about VLOOKUP. If the teacher fills
in a couple of students' names in the roster, like this:
A B C D
Bentley, Robert
Branning, Elizabeth B
Harking, Stephen
Homir, Nemo
Lemore, Louis
...then all you need to put in cols B, C, D etc is a VLOOKUP function that
looks up the value in column A in the repository table in that other
workbook. See below for some explanation of VLOOKUP, and of course it's in
the Excel documentation too.
--- "Mark" wrote:
Yes, the list box populates correctly and the teacher is allowed to make
multiple selections, prior to clicking on an 'Add Students' command button on
the form and then the students are added to the roster (name only with this
code).
Private Sub CommandButton1_Click()
Dim LastRow As Object
' Loop through the items in the ListBox.
For x = 0 To ListBox1.ListCount - 1
' If the item is selected...
If ListBox1.Selected(x) = True Then
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = ListBox1.List(x)
Else
Unload Me
End If
Next x
End Sub
So, the two issues/questions that I have are, first, how can I populate or
display the list box with only the names of the students or is that even
possible? Secondly, how do I retrieve the record of the test scores
associated with the selected name, if I don't retrieve the entire record
initially? Right now, I can make it work, but it's convoluted and appears to
be very poor coding on my part. Am I making any sense or just confusing the
issue?
:
Ah, you're using a form! You say it's preliminary so I guess you've at least
tried it and it's populating the ListBox, right? So that much is right.
Then what? You get your code to put the selected name in column A of the
current row, and then display the form again, 'round and 'round until the
teacher clicks on the No-More-Students button meaning that all the names are
now in place. Right?
Or what? See, you've told me here what you intend, but so far I don't hear
a question....?
--- "Mark" wrote:
I think we're close, but I'm a real idiot when it comes to Excel VBA, so
I'll attach the code (I have changed some of the names since my original
post) that I'm using to populate the list box. This is just preliminary and
on my home machine only:
Private Sub UserForm_Activate()
Dim wkbk As Workbook
Dim rng As Range
Application.ScreenUpdating = False
Set wkbk = Workbooks.Open(FileName:="C:\Documents and Settings\HP_Owner\My Documents\Repository.xls")
With wkbk.Worksheets("Grade 4")
Set rng = Intersect(.Range("A:Z").EntireColumn, .UsedRange)
End With
UserForm1.ListBox1.ColumnCount = 26
UserForm1.ListBox1.List = rng.Value
wkbk.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
What I'm trying to do is simply populate the list box with the names and
reduce the clutter in it, which I can do by changing the range to "A:A", yet
still retrieve the data associated with the name (from the repository
workbook) and populate the new spreadsheet. I'll have to play with the
VLOOKUP function to see if I understand it and I do appreciate your help.
:
The fact that the test scores are in a separate workbook is a minor
complication only; VLOOKUP can still look them up for you, it just requires
a little additional information in the lookup-table argument to tell Excel
where to look for the table data. But it sounds like you've changed
questions: In the original post, I understood you to have already
populated the list box and "My question is, how do I get the available
columns of test scores to populate in the adjoining columns of the new
classroom roster?".
But in this round it sounds like you want to know whether "it were possible
to populate the list box with all 1100 students via an array and suppress the
remaining columns". So increasingly I think I don't know what you really
want. I don't use list boxes much, so I can't tell you what the population
limit might be. But I should think you've already tried it and know the
answer, so I'm not sure what you were saying here. And I don't know what
you meant by "suppress the remaining columns", either. You'll have to
rephrase it, I'm afraid, or explain more.
As for the VLOOKUP, you can look it up to get more information if you've
never used it but basically the call is VLOOKUP(<Text>,<Table>,<RelCol>,
0). The <Text> is the student's name that you're looking up, the <Table>
is the table of student names and grades you're looking it up in, <RelCol>
is the column you want to retrieve once VLOOKUP has found the student's
name and 0 means the table is not necessarily sorted on student name
and you want an exact match on the name, not just something close. (Of
course I realize you may not be doing the lookup on the student's name;
you could be using student number or something, but I figure you can
adapt.) What we're looking at here is how to specify the table:
If the table is somewhere in the same worksheet, you just have to specify
the rows and columns:
VLOOKUP(<Text>,N1043,<RelCol>,0)
If the table is in the same workbook but a different worksheet, you have to
specify the worksheet's name like this:
VLOOKUP(<Text>,WorksheetNam!N10
...or, if there's a space in the worksheet name,
VLOOKUP(<Text>,'Worksheet name'!N10
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
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