VBA Code to Use "Name" in a cell as a reference

[SeventhFloorProfessor]

Excel 2007 VBA

I've tried looking all over for this, but I cannot find an example that
works. Any help would be appreciated. I'll try to make this simple. The file
names and rows/columns are not real, they're examples.

I am running a code that uses two workbooks. One workbook, "Test Results",
is a .csv file exported from a testing program; it has three columns: student
name, ID numbers, and a "1" (correct) or "0" (incorrect) for each question on
the test. It looks like this (the number to the far left is the row number,
for your reference here only):

A B C
1 Student A 409 1
2 Student A 409 0
.... it will continue on like this for each question ...
21 Student B 426 1
22 Student B 426 1

This list can go for quite a while (30 students x 40 questions = 1200 rows).

The workbook with the macro, "Assessment Template.xlsm", is an assessment
tool. Its job is to list whether each student got the question right or wrong
(each question is tagged with a learning standard or other data tag), then
report the result back. I was able to make a macro that will get the
student's scores from "Test Results.csv" and place them in "Assessment
Template.xlsm" because the student's score were listed vertically, in order,
and the assessment sheet just had them in the same order, horizontally, so
the program cycled using a Do Loop.

"Assessment Template.xlsm" looks like this:

A B C D
1 Student A 409 1 0
2 Student B 426 1 1

However, when more than one class takes the test, the program (for whatever
reason) simply puts both classes in the same CSV file, alphabetized by name
and not distinguished by class, so I now have a need to tell the macro to do
the following:

Look in Column A in Assessment Template.xlsm", take the student name, then
look in "Test Results.csv" and search for the student's name... from there
on, I assume I can run my DO Loop to have the program repeat this procedure
for every row of "Assessment Template.xlsm" as I have been.

Also, I have learned to tell the program this:

"Dim i
i=500
if ActiveCell.value >= i Then"

So that if the value is greater than 500, it will performa specific action.
How do I set a range, "if the value of the active cell is between two
points"? A more visual example:

"Dim ihigh
Dim ilow
ihigh=500
ilow=400
if ActiveCell.value >= ilow AND if ActiveCell.value <= ihigh Then"

This would allow me to have the program do some thing based on the student's
ID number...

I appreciate your help!!

 

[Luke M]

To store students name, simply define a variable as
xName = activecell.value 'assuming the active cell is a cell in A.

you could of course change the right-hand side of equation to whatever you
need to pick the correct thing. Then later, have the program find xName by
reverse

For each cell in range("B1"E1") 'whatever range you want to search
If cell.value = xName then
'what you want done when found
end if
next cell


As for your later question, a simple rewording:
If ActiveCell.value >= ilow And ActiveCell.value <= ihigh Then

is the correct syntax to state what you want.