• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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!!!
0
simondopickup
Asked:
simondopickup
  • 3
  • 2
1 Solution
 
jeveristCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now