Defining Range with SET keyword

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
Hello,

I'm trying to define two ranges using the "SET" keyword function in VBA, but my code does not seem to be working. What I want to do is to be able to activate the macro from any worksheet or cell in the workbook and the macro should take me to the defined range. However everytime I run the macro from another sheet I get the following error:

""Run-time error '1004': Application-defined or object defined error"

Also the macro only seems to work properly when I activate it from cell A1 of the "SAP Refined Data" worksheet.

My code is as follows:

Code:
Sub ConversionRange()

Set myRange = Sheets("SAP Refined Data").Range("1:1")
Set cRange = myRange.Find(What:="Cost Element").Offset(1, 0).Range(Selection, Selection.End(xlDown))

cRange.Select

End Sub

Can anyone advise me on where I'm going wrong?

Thanks
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Not sure if i understood what you need...maybe...

Code:
Sub ConversionRange()
    Dim myRange As Range, cRange As Range
    With Sheets("SAP Refined Data")
        .Activate
        Set myRange = .Range("1:1")
        Set cRange = myRange.Find(What:="Cost Element").Offset(1, 0)
        Set cRange = Range(cRange, cRange.End(xlDown))
        cRange.Select
    End With
End Sub

M.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
You can only use .Select or Selection to reference cells on the Active Sheet.

This should work because you are just setting a range variable (no selection)
Set myRange = Sheets("SAP Refined Data").Range("1:1")

This will only work if The Active Sheet is "SAP Refined Data" because you are referencing Selection which only applies to the currently active sheet.
Set cRange = myRange.Find(What:="Cost Element").Offset(1, 0).Range(Selection, Selection.End(xlDown))

Assuming you were to set cRange to a cell on another sheet, the .Select method doesn't allow you to select the sheet and range like this.
cRange.Select

Try something like this...
Code:
[color=darkblue]Sub[/color] ConversionRange()

    [color=darkblue]Set[/color] myRange = Sheets("SAP Refined Data").Range("1:1")
    
    [color=darkblue]Set[/color] cRange = myRange.Find(What:="Cost Element").Offset(1, 0)
    
    [color=darkblue]Set[/color] cRange = Range(cRange, cRange.End(xlDown))
    
    Application.Goto cRange

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
Hello Marcelo,

The code that you gave me does work somewhat but not completely.

My main goal with this macro is to define the sheet name and location of the two ranges; which are [color]myRange[/color] and [color]cRange[/color].

The range:[color]myRange[/color] should be fixed as row-1 on the "SAP Refined Data" worksheet.

The range of [color]cRange[/color] should be the data under the column heading "Cost Element" which is in row-1 on the sheet name "SAP refined Data.

The reason for this is that I would like to be able to reference the two ranges when when I expand the macro. I tested your code by expanding it a little bit and it still gave me an error message.

The expanded code is below:

Code:
Sub ConversionRange2()
    Dim myRange As Range, cRange As Range
    With Sheets("SAP Refined Data")
        .Activate
        Set myRange = .Range("1:1")
        Set cRange = myRange.Find(What:="Cost Element").Offset(1, 0)
        Set cRange = Range(cRange, cRange.End(xlDown))
        cRange.Select
    End With

Sheets("Sheet1").Select
cRange.Select

End Sub[/Code}

When the code moves to a different sheet and tries to come back to [color]cRange[/color], it results in an error message.
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
Hello Marcelo,

I stand corrected. Your code does seem to be working as I need it to.

Alphafrog, your code worked as well.

Thank you both for your help.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,433
Messages
5,596,098
Members
414,042
Latest member
Swiftkoala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top