Solved

Return a value of the named range

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 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