Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Use "Name" in a cell as a reference
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Use "Name" in a cell as a reference
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "SeventhFloorProfessor" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
"Indirect" reference to a cell in a code: how to do it? | Excel Programming | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming | |||
Excel VBA - go to worksheet "name" based on a cell reference ="Name" | Excel Programming |