VBA/EXCEL/Running Records for Teachers

Hi RWahlen,

you could delete the question vb because it's the same and i guess your answer will show up in this thread sooner

:O)Bruintje

Bruintje,

Thanks for the tip -I deleted the question in VB and hope to get an answer soon!

already looking into it and i got a question

what you're trying to do here is
-get some running totals for the individual
-on the individual sheet
-from the running records data sheet

is that correct?

:O)Bruintje

Bruintje,

Yes, some of the goals you mentioned are correct.
One the the objectives will be for the teacher to keep
a history of the records (from lower level books), but
be able to pull the most current records from the whole
class. We could not come up with a formula for this
problem.  We take assessment records from the
class (about 30 students) throughout the year. We need
the old records, from Sept/Oct/Nov/Dec/ and now that it is
almost April, we will want to pull only records from the
end of March to group the students into appropriate
reading groups. Does this help explain what we are
looking for?

Thanks,
Ron

Let me try to restate, Ron,

You want on worksheet "Individual", from row 5 to row (number of students + 5 -1),

the information about the student entered in column A of that row, from the worksheet "running records data", but only the latest book worked on, and not showing the blank books neither.

Is that correct?

If yes, where should that information be positioned on sheet "Inidividual"?

calacuccia

Yes Calacuccia!

This is the only information
that we want to move over to the
individual spreadsheet. The individual
spreadsheet needs to be re-done. There
will have to be a place where the user
can type in a name of the student and
this information will be pulled over:


1. title of book
2. date
3. AR
4. M
5. s
6. V
7. R


Thanks!
Ron

that's a lot more specific ;)

It is a bit clearer at least, Ron. ;-)

But, the "running records data" sheet will have to be redone as well probably. From book 11-12 on, there is no date column yet, which is important for the 'to be used' formula to pull records from this sheet. As you will add date columns, the to be retrieved columns will be shifted to the right, and the formula must probably take this into account.

Especially, this bothers me, since I would probably use the column '#E' of each book to determine if there is data present or not.

Calacuccia,  

I am not very clear on how exactly you would use column '#E' to determine if there is data present or not. Why would you use column '#E'?

I tried to put the running records sheet into a sheet which has the data across the columns so that I could use a function to read (i.e.) columns B-AK. But this didn't work well with all of the data we need to collect.

Ron

Ron,

I would use columns #E because it is the first column of each book which is empty if the student did not take the book yet

I am not referring to column E but to columns D, M, V, ... and al the other columns which have value #E on line 6 of it.

calacuccia

Calacuccia,

Is it possible for you to show me this in the Excel file and email it to me.

Thanks,
Ron

Ron,

I'll paste my work instead.

I've developped the following UDF for you (UDF = User Defined Function). This is a VBA function written for use on your Excel worksheet.

It has 2 arguments,

mName which is the name of the student, and
mArgument which is a value of 1 to 7 indicating what data needs to be retrieved.

You call it like this, on your individual sheet, for example in cell B5, with Abe filled in in A5

=LastBook(A5,1)

The function must be pasted in your module1 of your VBA part, and is below my signature.

Any questions, please ask back,

calacuccia

Function LastBook(mName As String, mArgument As Integer) As Variant
Dim RRS As Worksheet
Dim aRow As Long, aCol As Integer
Set RRS = ThisWorkbook.Worksheets("running records data")
On Error Resume Next
aRow = Application.WorksheetFunction.Match(mName, RRS.Range("A:A"), 0)
If Err.Number <> 0 Then
    Err.Clear
    GoTo ErrHandler
End If
On Error GoTo 0
For j = RRS.Range("IV6").End(xlToLeft).Column To 2 Step -1
    If RRS.Cells(6, j) = "#E" Then
        If RRS.Cells(aRow, j) <> "" Then
            aCol = j
            j = 1
        End If
    End If
Next j
If j = 1 Then GoTo NoData
Select Case mArgument
Case 1 'Title of Book
    LastBook = RRS.Cells(5, aCol - 1)
Case 2 'Date
    LastBook = RRS.Cells(aRow, aCol - 2)
Case 3 'AR
    LastBook = RRS.Cells(aRow, aCol + 2)
Case 4 'M
    LastBook = RRS.Cells(aRow, aCol + 3)
Case 5 'S
    LastBook = RRS.Cells(aRow, aCol + 4)
Case 6 'V
    LastBook = RRS.Cells(aRow, aCol + 5)
Case 7 'R
    LastBook = RRS.Cells(aRow, aCol + 6)
End Select
Exit Function
'Last part is only in case of errors
ErrHandler:
LastBook = "Error"
Exit Function
NoData:
LastBook = "Empty"
End Function

Hi RWahlen,

Any luck yet to try the suggestions in this thread? Please provide some feedback to the experts willing to spend their time and try to solve your problem.

Thanks
:O)Bruintje

Bruintje,

I did receive the code from Calacuccia but I do not know where in excel to place it.

Thanks,
Ron

Brian,

This problem could still use some work. I am unfamiliar at this time how to enter code or VBA into Excel. Perhaps, if you can explain this to me - I will be able to solve this one.

Thanks,
Ron