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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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,166
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
 

Forum statistics

Threads
1,081,932
Messages
5,362,214
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top