Using a variable to refer to a named range

[joey peanuts]

Excel 2007

I am getting an error on the bolded line. I have used Intersect successfully in other areas of my code. I think the problem is with my attempt to use the value of ExactRange1 as a Range object. In my trial, ExactRange1 becomes the name of a valid named range ("RBARECT_STYLE").

Sub RbaRectRetail()
Dim ExactRange1 As String, ExactRange2 As String, ExactRange3 As String, ExactRange4 As String, ExactRange5 As String
Dim SpatialRange1 As String, Tax As String, Comm As String, Marg As String, Disc As String
Dim lineNUMBER(60) As String
Dim Erange1VALUE(60) As String
Dim Erange2VALUE(60) As String
Dim Erange3VALUE(60) As String
Dim Erange4VALUE(60) As String
Dim Erange5VALUE(60) As String
Dim Srange1VALUE(60) As String
Dim taxVALUE(60) As String
Dim commVALUE(60) As String
Dim margVALUE(60) As String
Dim discVALUE(60) As String

For Each cell2 In Worksheets("RBARECT_RETAIL").Range("RBARECT_FORMULA_NAME").Cells

If cell2.Value <> "" Then

ExactRange1 = cell2.Offset(0, 1).Value
​

ExactRange2 = cell2.Offset(0, 2).Value
ExactRange3 = cell2.Offset(0, 3).Value
ExactRange4 = cell2.Offset(0, 4).Value
ExactRange5 = cell2.Offset(0, 5).Value
SpacialRange1 = cell2.Offset(0, 6).Value
Tax = cell2.Offset(0, 7).Value
Marg = cell2.Offset(0, 8).Value
Comm = cell2.Offset(0, 9).Value
Disc = cell2.Offset(0, 10).Value
For Each cell1 In Worksheets("RBA_RECT").Range("RBARECT_LINE").Cells

If cell1.Value <> "" Then

lineNUMBER(cell1) = cell1.Value
Erange1VALUE(cell1) = Intersect(Range(Cells(cell1.Row, 1), Cells(cell1.Row, 100)), Range(ExactRange1)).Value
​

End If
​

Next cell1
​

End If
​

Next cell2
End Sub

Thanks for any suggestions.

 

[AlphaFrog]

There's a lot going on in your code. It's hard to follow without seeing your data structure and knowing the Named range references. Having said that, here are a couple of guesses...

If the value in cell1 is not between 0 and 60 (the dimension of Erange1VALUE), you'll get an error.

IF the intersect returns more than one cell or no cells, you'll get an error.

If the intersect does return more than one cell, you could reference just the 1st cell in the intersect like this...

Erange1VALUE(cell1) = Intersect(Range(Cells(cell1.Row, 1), Cells(cell1.Row, 100)), Range(ExactRange1))[COLOR="Red"](1)[/COLOR].Value

Forum tip:
It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE] It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

 

[joey peanuts]

There's a lot going on in your code. It's hard to follow without seeing your data structure and knowing the Named range references. Having said that, here are a couple of guesses...

If the value in cell1 is not between 0 and 60 (the dimension of Erange1VALUE), you'll get an error.

IF the intersect returns more than one cell or no cells, you'll get an error.

If the intersect does return more than one cell, you could reference just the 1st cell in the intersect like this...

Erange1VALUE(cell1) = Intersect(Range(Cells(cell1.Row, 1), Cells(cell1.Row, 100)), Range(ExactRange1))[COLOR=red](1)[/COLOR].Value

Forum tip:
It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE] It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

The named range is a 1 column range and of course the row is 1 row. So, I think by definition, it will only return 1 cell. It's true the cell could be empty. All of these ranges are exactly 1 column wide by 60 rows deep, so I shouldn't run into any problems there (my arrays start at 1, not 0). You don't see a problem using a the string variable Exactrange1 in the Range() object?

 

[AlphaFrog]

You don't see a problem using a the string variable Exactrange1 in the Range() object?

If the string variable is a Named range as you indicated, then it should work.

As a quick test, you could use...

MsgBox Range(ExactRange1).Column

...to display the column number of the named range and prove the reference is valid.


Also, instead of using intersect to get the row\ column intersect of the two ranges, you could use this...

Erange1VALUE(cell1) = Cells(cell1.Row, Range(ExactRange1).Column).Value

 

[joey peanuts]

If the string variable is a Named range as you indicated, then it should work.

As a quick test, you could use...

MsgBox Range(ExactRange1).Column

...to display the column number of the named range and prove the reference is valid.


Also, instead of using intersect to get the row\ column intersect of the two ranges, you could use this...

Erange1VALUE(cell1) = Cells(cell1.Row, Range(ExactRange1).Column).Value

This worked great. My problem is I need to use the Worksheets function because I was jumping around. Thanks for your help. Your code is my efficient than using Intersect.