Gingertrees
Well-known Member
- Joined
- Sep 21, 2009
- Messages
- 697
Doing a daily report, in which I import 2 sheets to be processed, we'll call ClassReport and GradeData.
Both have a varying number of rows, but have the same general setup each day. Say I want to pull in grade percentage for each student into Class Report, doing a vlookup referencing Grade Data.
Trying to automate this all into VBA, so please bear with me:
ClassReport:
<tbody>
</tbody>
GradeData:
<tbody>
</tbody>
(Sure there's only one kid with each name in the class - roll with it.)
Sometimes there's 15 rows in Grade Data, other times there's 20, etc.
So I put my vlookup in Class Report:
=vlookup(C2,'GradeDataSheet8-18'!B4:C9,2,False)
I usually just fill across, so I have to add Absolute references after I type that. (...$B$4:$C$9)
Two questions:
1) Since I'm trying to automate this into a macro, how would I establish the range in GradeDataSheet every day? If it was the same thing in the same sheet, I'd define the name as "GRADES" and sub that into my vlookup.
I already have figured out how to do a LastRow and LastColumn declaration on there, so I could use that, but how do I get my code to recognize that today's sheet is the 8-18 one?
2) If I am able to answer question #1, how can I use Absolute references with variables like LastColumn (an integer)?
Both have a varying number of rows, but have the same general setup each day. Say I want to pull in grade percentage for each student into Class Report, doing a vlookup referencing Grade Data.
Trying to automate this all into VBA, so please bear with me:
ClassReport:
vlookup-goes-here | " | " | " | ||
Data | Etc. | Jenny (C2) | Sam(D2) | Amy(E2) | Chris(F2) |
... | ... | data | data | etc. | ... |
... | ... | ... | ... | ... | ... |
<tbody>
</tbody>
GradeData:
Assign | ment | |
Date | ||
House | Name | % to date |
x | Amy | 95 |
x | Chris | 82 |
x | Dani | 94 |
x | Jenny | 88 |
x | Marc | 88 |
x | Sam | 78 |
<tbody>
</tbody>
(Sure there's only one kid with each name in the class - roll with it.)
Sometimes there's 15 rows in Grade Data, other times there's 20, etc.
So I put my vlookup in Class Report:
=vlookup(C2,'GradeDataSheet8-18'!B4:C9,2,False)
I usually just fill across, so I have to add Absolute references after I type that. (...$B$4:$C$9)
Two questions:
1) Since I'm trying to automate this into a macro, how would I establish the range in GradeDataSheet every day? If it was the same thing in the same sheet, I'd define the name as "GRADES" and sub that into my vlookup.
I already have figured out how to do a LastRow and LastColumn declaration on there, so I could use that, but how do I get my code to recognize that today's sheet is the 8-18 one?
2) If I am able to answer question #1, how can I use Absolute references with variables like LastColumn (an integer)?