Use of Named Range in VBA - Method 'Range' of object '_Global' Failed

[hurst2008]

I have a macro that copies a spreadsheet, renames the copy spreadsheet then deduplicates the data range based on a code I've assigned so that only unique items accross the branch remain no matter where they are sold in the branch...it has always worked well using a series of Indirect formulas to create the specific range reference appearing on a helper "Macro Inputs" worksheet that was used in the macro, but using the Indirect function has left me with a worbook that is difficult to maintain because every change requires that I update the static Indirect references. To correct this I created a named range that uses the worksheet name that the copy of the worksheet will become (but it does not exist when the workbook is opened). But, I can't get the macro to work and I cannot figure out why even after many many internet searches...I am receiving a Method "Range" of object'_Global' Failed message with the Set deDupRange line highlighted. Any help you can give me would be appreciated...I am an accountant and not a trained developer (obviously), so I would need a solution to fit that level of knowledge.


Here is the formula I've used to create the dynamic PriceReviewEngineDataRange named range at the workbook level: =PriceReviewEngineByConcept!$A$31:INDEX(PriceReviewEngineByConcept!$DZ:$DZ,COUNTA(PriceReviewEngineByConcept!$A:$A)-COUNTA(PriceReviewEngineByConcept!$A$1:$A$30)+ROW(PriceReviewEngineByConcept!$A$30),1)

Here is the Macro that I'm trying to use the named range in:
Sub conceptLevelReview()
'
' conceptLevelReview Macro
'
Dim sweepTab As String
Dim sweepTabTemp As String
Dim sweepTabConcept As String
Dim deDupRange As Range
Dim indexMatchRange As String
Dim Ans As Integer
Dim rvcCol1 As Integer
Dim rvcCol2 As Integer
Dim qtySoldCol As Integer
Dim priceCol As Integer
Dim discCol As Integer
Dim grossSalesCol As Integer
Dim netSalesCol As Integer
Dim deleteRowRange As String

Ans = MsgBox("WARNING: This macro should only be used before entering new prices or comments as they will be overwritten...do you want to proceed?", vbYesNo)
Select Case Ans

Case vbYes

sweepTab = Sheets("Macro inputs").Range("B126")
sweepTabTemp = Sheets("Macro inputs").Range("B130")
sweepTabConcept = Sheets("Macro inputs").Range("B129")

indexMatchRange = Sheets("Macro inputs").Range("B132")
rvcCol1 = Worksheets("Macro Inputs").Range("B133")
rvcCol2 = Worksheets("Macro Inputs").Range("B134")
qtySoldCol = Worksheets("Macro Inputs").Range("B135")
priceCol = Worksheets("Macro Inputs").Range("B136")
discCol = Worksheets("Macro Inputs").Range("B137")
grossSalesCol = Worksheets("Macro Inputs").Range("B138")
netSalesCol = Worksheets("Macro Inputs").Range("B139")

Sheets("Macro inputs").Calculate
Sheets(sweepTab).Select
ActiveSheet.Calculate
ActiveSheet.Unprotect "core"
With Worksheets(sweepTab)
If .AutoFilterMode Then
If .FilterMode Then
ActiveSheet.ShowAllData
End If
End If
End With
ActiveWorkbook.Unprotect
Sheets(sweepTab).Copy Before:=Sheets(6)
Sheets(sweepTabTemp).Select
Sheets(sweepTabTemp).Name = sweepTabConcept
Set deDupRange = Range("PriceReviewEngineDataRange")
deDupRange.Select
Selection.RemoveDuplicates Columns:=120, Header:= _
xlNo

[patel45]

can you attach your file ?

[hurst2008]

I'm sorry, it's a large comoplicated workbook for work and I don't have permission to share it.....I was hoping the issue was obvious to a more experienced user (this is my first time using a named range in a macro).

[Norie]

Where does the code actually error?


PS Can you edit your post and add code tags? That makes the code a lot easier to read.

[hurst2008]

It errors at the third line from the bottom.... Set deDupRange = Range("PriceReviewEngineDataRange")

[hurst2008]

And that's not the entire code....I only posted up to the point where the error occurs, so the other defined variables relate to actions that occur later in the code.

[Norie]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[hurst2008]

Please Login or Register  to view this content.

[Izandol]

You may try:

Please Login or Register  to view this content.

Also you can define a name that uses EVALUATE instead of INDIRECT and it will then work with dynamic ranges.

[hurst2008]

I tried the suggestion but now receive an Application-defined or object-defined error...

[Norie]

Have you checked that the formula for the named range is returning a valid range?

[hurst2008]

When the workbook is opened it would not be a valid range because the worksheet doesn't exist...but at the point that it is used in the macro the worksheet does exist and should return a valid range. Once the macro errors out I can go into the PriceReviewEnginebyConcept worksheet, go into the Name Manager, select the named range and place my cursor in the formula box and see that the correct range is selected and so I know it is valid at the point the macro errors. So, specifically at the following point in the code the range should become valid:

Please Login or Register  to view this content.

[Norie]

When you step through the code just before you reach the line causing the error what does this return if you enter it in the Immediate Window (CTRL+G)?

Please Login or Register  to view this content.

PS Try it without the sheet reference to.

[hurst2008]

With the sheet I receive a runtime error 9 subscript out of range
When I remove the sheet reference I receive an Application-defined or object-defined error

[hurst2008]

And, I did try adding a calculation between when the sheet name changed (when the named range should have become valid) and it didn't change the result....I did that thinking the Named range needed to refresh or something like that

[Norie]

Then that sounds like the formula in the named range isn't returning a valid range reference.

In the code are you changing the sheet name that's used in the formula?

[hurst2008]

The original sheet is named PriceReviewEngine and after the copy step the new sheet is PriceReviewEngine(2), then I change it to PriceReviewEngineByConcept.....the named range used PriceReviewEngineByConcept. I created that named range by first referencing the PriceReviewEngine sheet and then editing the formula to refer to PriceReviewEngineByConcept even though the worksheet didn't exist. I can use an if statement and helper cell to switch the name of the sheet in the named range once it exists which is basically what I had always done....But, that means I have to use an Indirect reference to combine the two pieces (Worksheet Name and Range) and I don't know how to create a dynamic range when I have to use Indirects since the range reference is static in the "".....I've been able to get the row number to change, but not the column letter references by using the following formula:

Helper Cell to create non named deDupRange previoulsy ="$A$"&B127&":$DV$"&B128
B127 contains a formula to determine the first row and B128 contains a formula to determine the last row

[hurst2008]

And I need the range to dynamically adjust for additional data (length) and whenever columns are added or deleted so that I can improve/maintain the workbook more easily.

[Norie]

Do you use the named range anywhere else?

[hurst2008]

I will not be using that named range anywhere else...it's created solely to support the deduping step of the macro although I have other steps that also require different ranges of the same sort (so this was my first attempt to try to correct maybe four ranges that all need to adjust with changes to the spreadsheet).

[Norie]

Instead of using a dynamic named range why not create the range in the code?

With something like this perhaps, which would need to go before you change the sheet name.

Please Login or Register  to view this content.

[hurst2008]

Maybe I should just go back to using Indirects + helper cells, but figure out to get a new helper cell to return a column letter reference. I know how to return a column number, but not a column letter.

[hurst2008]

I want to be able to add and remove columns and have the code update.....we add new features (analyses) to the spreadsheet which require adding columns in the appropriate location of the spreadsheet which is sometmes the middle. So I need the code to update for those changes automatically.

[Norie]

What exactly should DeDup be?

Could the no of columns as well as the no of rows change?

[hurst2008]

Yes, the number of columns will change and so I was going to set my last column reference to DZ and then as I added columns I need it to update to EA, EB, EC, etc. I also don't think the count method I used to obtain the last row of data could also be used for the last column since there are blanks in between and if I use countblank then it would count all the columns after those that I need included in the range.

[Norie]

You could use code to get the range.

Please Login or Register  to view this content.

[hurst2008]

I'll try that, thank you.