Solved

Return a value of the named range

Posted on 2007-11-18
5
180 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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

696 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