Return a value of the named range

Experts,

I have a named range called "MTTR" and i want to return a value from this range that corresponds to the index number , t_ind. I assume i can treat the range like a 1 dim array but so far this isnt compiling. the named range "MTTR" is a range of 1 row and several colums.

t_rep = ((b - 6) * (0.2 * [MTTR].Cells(1, t_ind))) + [MTTR].Cells(1, t_ind)

Any suggestions?

Cheers!!!
simondopickupAsked:
Who is Participating?
 
jeveristConnect With a Mentor Commented:
Hi simondopickup,

>  I assume i can treat the range like a 1 dim array but so far this isnt compiling

You can.  What compile error are you getting?

This works for me with [MTTR] = range H1:P1 of numbers:

Sub ValueFromNamedRange()
Dim t_rep As Double, t_ind As Long, b As Long

t_ind = 2
b = 12

t_rep = ((b - 6) * (0.2 * [MTTR].Cells(1, t_ind))) + [MTTR].Cells(1, t_ind)

End Sub

Jim
0
 
simondopickupAuthor Commented:
Error message = 'object variable or with block variable not set. - still not executing.

The named range "MTTR" is a list of numbers in cell range  A10:D10 - but this varies depending on user inputs - so the range is defined as "MTTR" at each execution.

Simondo
0
 
simondopickupAuthor Commented:
Can anyone help??!?!?!?!  if the named range is in a different sheet will this cause a problem???
0
 
jeveristCommented:
Simondo,

>  if the named range is in a different sheet will this cause a problem?

It would if it was in a different Workbook but a different sheet within the same workbook should be OK.  Check to see if [MTTR} is defined more than once somewhere else in the workbook.

Did the code posted above also cause an error?

Jim
0
 
simondopickupAuthor Commented:
Sorry i was overlooking something on your solution. All fixed, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.