Named range row/column reference

U

Urklnme

Hello,

I am using named ranges in a VBA macro

StudentNbr is A26:A45
TestWeek is E25:X25
TestScores is E26:X45

As you can see this is a grid of data with student numbers down th
left side, test week numbers across the top and the grid containin
test scores.

I would like to update the cells in TestScores based on the positio
of the values in the StudentNbr and TestWeek named ranges using:

Range("TestScores") (vStudentNbr,vTestWeek).value = vTestScore

*Is there a way to get vStudentNbr and vTestWeek as relative reference
to the beginning of their respective named ranges? *

example: where vStudentNbr = 1 refers to the first row of StudentNb
and therefore the first row of TestScores

I can locate the correct cell within the StudentNbr and TestWeek name
ranges by comparing against the values inside

but I cannot seem to get the row/col numbers as an offset

Can you assist
 
B

Bernie Deitrick

Urklnme,

You can more easily find it in absolute rather than relative terms:

Cells(Range("StudentNb").Find(vStudentNb).Row, _
Range("TestWeek").Find(vTestWeek).Column).Value = vTestScore

HTH,
Bernie
MS Excel MVP
 
Top