Solved

Return a value of the named range

Posted on 2007-11-18
5
181 Views
Last Modified: 2010-08-05
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
Comment
Question by:simondopickup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 38

Accepted Solution

by:
jeverist earned 500 total points
ID: 20309779
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
 

Author Comment

by:simondopickup
ID: 20311484
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
 

Author Comment

by:simondopickup
ID: 20311799
Can anyone help??!?!?!?!  if the named range is in a different sheet will this cause a problem???
0
 
LVL 38

Expert Comment

by:jeverist
ID: 20312603
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
 

Author Closing Comment

by:simondopickup
ID: 31409854
Sorry i was overlooking something on your solution. All fixed, thanks!
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question