VBA for setting range to variable carrying dynamic name range & with indirect function

Hi, i am working on a user defined function.
The function has 2 tasks:
1st to identify the name range out of 21 name ranges basis user input (user input 1) and use it in set range = variable (this is where i need help)
I think here directly referring to the range name also will need indirect function support which i am again not able to do and need help.
2nd then to match the string (user input 2) in the name range defined above and return the adjacent columns (which i am able to do).

The function code i have written does pretty much the task 2, but with only one name range which stays static.

Code begins:
------------------------------------------------------------------------------------
Function lookupdiv(dimd As String) As Variant
Dim rngs As Range
Dim m As Integer

'--- Load the table (range) from the global name
'--- the problem is in the below code line where i do not know how to set rngs to
'----multiple names basis some user input. Here i am able to use only named range called
'-----"Dim_2G3G". However, i have 21 name ranges like this. Also, i think the range reference
'------will require indirect functionality to be used

Set rngs = ActiveWorkbook.Names("Dim_2G3G").RefersToRange


'--- Iterate through all members in the table
'--- (skip first header row),

For m = 2 To rngs.Rows.Count
If (dimd = rngs(m, 1)) Then
'--- A match has been found. Load data end exit the function
lookupdiv = rngs(m, 3)
Exit Function
End If
Next m

'--- if the exeution reach this point, there was no match
'--- Return "error" value
lookupdiv = 0
End Function
--------------------------------------------------------------
Code ends

This is my first post in the forum and i am eagerly looking forward to the support. Thanks

Hi, my range is not one single range. But there are multiple range. Which range should be used is decided basis user input. Now i am unable to do the code for "rngs" variable range which will change everytime user will give some input. I need to define what would be the name "named range" of "rngs".
Hope i am able to clarify.

Hi, apologies for not being very clear. The user is going to specify input in cell in sheet "calculation". The input type will fall in different categories. these categories are different ranges or name ranges. for e.g. user input could be computer, tv, refridgerator then the range name is "electronics". If user input is bed or sofa or chair then range name is "furniture". My name ranges are "Dim_2G3G", "Dim_LTE", "Dim_Infra" which are corresponding to electronics, furniture in the above eg. Now with some formula or function, or sub, i will need to determine which if the input is TV then the range name is "electronics" and if the input is chain then the range name is "furniture".

Hi, yes, this code works but when i run it as separate function. When i did a common code for both steps, the output of function is #value!

here is the code i did:


Function lookupdiv(dimd As String, dimtech As String) As Variant
Dim rngs As Range
Dim m As Integer
Dim dimrange As Range

'--step 1 to identify the name range

Select Case dimtech
Case "2G", "3G", "General", "Combined"
dimrange = "Dim_2G3G"
Case "LTE"
dimrange = "Dim_LTE"
Case "Fixed"
dimrange = "Dim_Fixed"
Case Else
dimrange = "0"
End Select

'--- Load the table (range) from the global name
'--- the problem is in the below code line where i do not know how to set rngs to
'-----multiple names basis some user input. the below variable does not work and
'------ the return value for function is #value!

Set rngs = ActiveWorkbook.Names(dimrange).RefersToRange - the problem i think is in this code row


'step 2 to look into the name range for string match
'--- Iterate through all members in the table
'--- (skip first header row),

For m = 2 To rngs.Rows.Count
If (dimd = rngs(m, 1)) Then
'--- A match has been found. Load data end exit the function
lookupdiv = rngs(m, 3)
Exit Function
End If
Next m

'--- if the exeution reach this point, there was no match
'--- Return "error" value
lookupdiv = 0
End Function

Hi, i am sure this is not very tough... anybody who can help me out on this.

Attached the new file.
I do not know when you say tag code.. Am sorry.

i think the problem is in the line Set rngs = ActiveWorkbook.Names(dimrange).RefersToRange,
here i think indirect function is to be added since dimrange is not referring directly to the cells intend to use.

Thanks in advance.

Ok got it. here is the code with code tags

Please Login or Register  to view this content.

Hi Noori, any luck on the above code..?

Hello, can anyone help me with the code above...?