Problem with setting range in Macro

[floridagunner]

Hello,

I have written the following macro that is not working as intended:

Sub ConversionRange()

Dim myRange As Range
Dim cRange As Range

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

myRange.Select

End Sub

When activated, what I need the macro to do is go the worksheet "SAP Refined Data" and select the data below the cost element heading. However whenever I activate the macro from another worksheet I get the "Run-time error '1004': Application-defined or object defined error" message.

Can anyone point out what I might be doing wrong?

Thanks
floridagunner

[Medpack]

Hi Floridagunner,

The problem with your code is that you're telling it to search for a value in range "1:1" - Try the following (just adjust your search range accordingly if A1 to Z500 isn't big enough);

Sheets("SAP Refined Data").Range("A1:Z500").Find(What:="Cost Element").Select
Selection.Offset(1, 0).Select

Oh yea, forgot to also add incase you run it from another sheet - use at the begging;

Sheets("SAP Refined Data").Select

[floridagunner]

Hello Medpack,

The reason I have the code searching range ("1:1") is because that is where the "Cost element" field is located.

Secondly why do I have to specify the sheet name again when I have already specified that in the myRange attributes?

Thanks.

[Medpack]

Just include the sheet selection code I provided in the second code bubble. I'm not sure why Microsoft coded the vba this way but apparently the sheet needs to be the active sheet when issuing a select command.

[Medpack]

I'm also a little confused why you use the find function when you know the location of cost elements?

[floridagunner]

Hello Medpack,

Thanks again for your response. The reason I use the find function for cost elements is because although the table headings are always in row 1, they are not always in the same column, that is why the macro needs to find the cost element field first before it can select the data below it.

[protonLeah]

Try this:

Set cRange = Sheets("sheet1").Range("1:1").Find(What:="Cost Element")
Set cRange = Range(cRange.Address(0, 0)).Offset(1, 0).Resize(Cells(Rows.Count, cRange.Column).End(xlUp).Row)

I think you're trying to do too much with one line of code. That makes it hard to read and hard to update. Also, Who knows what "Selection" is referring to at the time you run the macro?