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