Using Cells to set range

[NdNoviceHlp]

Can't seem to get the right syntax for this. I'm trying to set a range for "C" to "F" for row "RoSpot". Dave

RowRange = Sheets("Sheet2").Range _
("Cells(RowSpot, 3): Cells(RowSpot, 6)")

 

[Richard Schollar]

As it is an Object variable, you need to use the Set keyword:

Set RowRange = Sheets("Sheet2").Range _ 
(Cells(RowSpot, 3), Cells(RowSpot, 6))

I have also removed the double quotes and replaced the colon with a comma. You are not using a string argument here.

Regards

Richard

 

[NdNoviceHlp]

Thanks Richard but I'm still getting an application or object defined error? Dave

 

[Richard Schollar]

Many apologies Dave - that ably demonstrates the danger in simply assuming one is right

I must admit I couldn't get it to work using the range/cells way of specifying a range

The closest I could get was via:

Sub test2()

Dim dobbin As Range

Range(Cells(1, 1), Cells(10, 1)).Name = "myRange"

Set dobbin = Range("myRange")

End Sub

There may be a simple reason as to why you can't do it as I had before, and hopefully someone with much more (&better!) knowledge than me will come back with the answer.

Richard

 

[Von Pookie]

I used to run into this all the time. Then I found out why: You need to specify the sheet for both cells. The cells(rowspot,3) is linked to Sheet2, but the cells(rowspot,6) does not have a sheet qualifier--so it is referring to whatever the activesheet is, by default.

If you leave your code as-is and run it with Sheet2 active, it should work.

If you want to run it and not worry about what sheet is active, you have to specify the sheet for each cell in the range (you can't have a range that has cells on different sheets).

Both of these statements do the same thing and are working for me:

Set RowRange = Sheets("Sheet2").Range(Sheets("Sheet2").Cells(RowSpot, 3), Sheets("Sheet2").Cells(RowSpot, 6))

With Sheets("Sheet2")
    Set RowRange = .Range(.Cells(RowSpot, 3), .Cells(RowSpot, 6))
End With

 

[Richard Schollar]

Kristy you are an absolute star - you have prevented me from tearing all my hair out! I couldn't figure out why it wouldn't work!

Thanks!

Richard

 

[NdNoviceHlp]

Kristy both codes worked perfect! Thank you for that and the reason! I tried both routes for literally hours before I posted and it was starting to get on my nerves. Like you Richard, I just didn't get it. Thank you for your efforts as well. This part of this project involves selecting the largest value range C:F in a row that is determined by a listbox selection(rowspot). So I'll post the other line of code you need to do this just for posterity. Dave

MaxRow = Application.WorksheetFunction.Max(RowRange)