Using Cell Numbers in a range

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Hi All,

I am trying to use row&col in a range. Code is as follows.
I get the errow application-defined or object-defined error

ShortRawMonFileName is correct and "Sheet1" is correct

Set source =
Workbooks(ShortRawMonFileName).Sheets("Sheet1").Range(ActiveSheet.Cells(1,
1), ActiveSheet.Cells(16, 5))

I also tried

Set source = Workbooks(ShortRawMonFileName).Sheets("Sheet1").Range(Cells(1,
1), Cells(16, 5))

Thanks
Chad
 
Here is all the code

Workbooks.Open MonDirectory & Bell100FileName, False
Set source = Workbooks(ShortRawMonFileName).Sheets("Sheet1").Range(Cells(1,
1), Cells(16, 5))
Set dest = Workbooks(Bell100FileName).Sheets("Import Data").Range("A1")
source.Copy dest
 
If shortrawmonfilename isn't the active workbook or if sheet1 is not the
activesheet, then your code will fail.

I'd use:

with Workbooks(ShortRawMonFileName).Sheets("Sheet1")
set source = .Range(.Cells(1, 1), .Cells(16, 5))
end with

or just:

Set source = Workbooks(ShortRawMonFileName).Sheets("Sheet1").Range("A1:E16")
 
I had that before, but I need to get deeper into the range code. What I
really need but am unable to get it to work either is this.

I need to select a 'floating' range of cells. By that I mean my range will
be from Cell(1,1) to Cell(1,5) and
Cell(5,1) to Cell(16,5)

But I will need to run it again but this time my range will be Cell(1,1) to
Cell(1,5) and
Cell(17,1) to Cell(19,5).

Basically I always need the first row selected and a group of other cells.
The problem is next week or so, my numbers will change and I don't want
hardcoded ranges that I have to change every week, so in my above ranges the
17 & 19 rows will have to be a variable.

Here is my hacked up chunk of code if it makes any sense. I think I am
missing a comma or a bracket somewhere

Set source = Workbooks(ShortRawMonFileName).Sheets("Sheet1") _
.Range(Cells(1, 1), Cells(1, 5), Cells(TotalMonitors(x), 1),
Cells(TotalMonitors(y) , 5))

Thanks
Chad
 
Try it with the "with" statement.

And remember to qualify each of those cells() and range()'s with that leading
dot.
 
Tried a new approach from the help menu, but it failed also

Set r1 = Range(Cells(1, 1), Cells(1, 5))
Set r2 = Range(Cells(17, 1), Cells(19, 5))
Set multirange = Union(r1, r2)
multirange.Select 'This worked

With Workbooks(ShortRawMonFileName).Sheets("Sheet1")
Set source = .Range(multirange) 'This didn't
End With
 
Back
Top