Named range row/column reference

[Urklnme]

again that is great.... but it is not quite what I am looking for....

I am trying to get the values for the references stored in th
variables so I can use them at my convenience.

I am writing a macro and must document it for people who know even les
that I do about Excel and VBA (got to love volunteer work!!).

This is why I am trying to make it clear as to the identity of th
elements, names, structures that I use.

Thanks for your assistance, you certainly are an MVP!

 

[Dave Peterson]

One more way:

Option Explicit
Sub testme()

Dim myRow As Variant
Dim myCol As Variant
Dim myStudentNumber As String
Dim myTestWeek As String
Dim wks As Worksheet

myStudentNumber = "$a$15"
myTestWeek = "$e$2"

Set wks = Worksheets("sheet1")
With wks
myRow = Application.Match(myStudentNumber, .Range("StudentNumber"), 0)
myCol = Application.Match(myTestWeek, .Range("testweek"), 0)

If IsError(myRow) _
Or IsError(myCol) Then
MsgBox "error on at least one of those keys!"
Else
MsgBox .Range("testscores")(myRow, myCol).Value
End If

End With

End Sub

My test data was all strings. If you use dates in the testweek range:

Dim myTestWeek As date
mytestweek = dateserial(2004,12,22) 'or whatever
myCol = Application.Match(clng(myTestWeek), .Range("testweek"), 0)