Syntax problem in Excel VBA 2007 range command

[phudgens]

I'm using the following code to try and define an offset range of cells, but VBA is not accepting the syntax. VBA accepts the WriteCell definition but not the Opers definition. I get a "Expected list separator or (" message at the . following "A2". Enclosing the "A2" and/or "A50000" in parentheses doesn't help. Thanks for any help,

    With Workbooks(ThisWkBk).Worksheets("Operators")
         Set WriteCell = .Range("A2").Offset(0, iOffset)
         Set Opers = .Range("A2".Offset(0, iOffset + 1), .Range("A50000".Offset(0, iOffset + 1).End(xlUp).Offset(1, 0)))
    End With

Paul Hudgens
Denver

 

[SkipVought]

hi,

 Set Opers = .Range(.[A2].Offset(0, iOffset + 1), .[A50000].Offset(0, iOffset + 1).End(xlUp).Offset(1, 0))

this is syntactly correct, but may not logically define your intended range.

If the column A range is contiguous to the last cell. then...

 Set Opers = .Range(.[A2].Offset(0, iOffset + 1), .[A2].end(xldown).Offset(0, iOffset + 1).Offset(1, 0))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Keeping closest to your code:
Set Opers = .Range([!].Range([/!]"A2"[!])[/!].Offset(0, iOffset + 1), .Range([!].Range([/!]"A50000"[!])[/!].Offset(0, iOffset + 1).End(xlUp).Offset(1, 0)))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[phudgens]

My column A is not continuous - your first suggestion above worked. I don't know why it worked, but it worked. Thanks,
Paul Hudgens

 

[phudgens]

PHV - Your suggestion above did not work. I get an "application-defined or object-defined error". I've used code similar to that many times before with no problems. For example:

With Workbooks(ProductionWkBk).Worksheets(XYWks)
     Set XYWnums = .Range("D1", .Range("D65536").End(xlUp))
End With

It is a puzzle to me why VBA will only accept the syntax in SkipVoughts example above when defining offset ranges.

 

[Gruuuu]

The .Range Method can work one of two ways. as you are likely aware, you can give it the address of a Cell:
[tt]
.Range("D2")
[/tt]

When you're defining an area as a span between two ranges, though, you need to specify two ranges inside of a range tag
Range(Range1, Range2)

You were trying to do this, but you were only specifying the text address. Excel doesn't realize you meant for that to be a range.

Do it like so, instead:
[tt]
.Range(.Range("D2"), .Range("D65536").End(xlUp))

[/tt]

Make sense?

 

[SkipVought]

A better method, that will translate seamlessly to 2007 code...

.Range(.Range("D2"), .Cells([b].Cells.Rows.Count[/b], "D").End(xlUp))

as 2007 has 1,048,576 rows.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

Thanks to everyone for your input. I expanded Gruuuus's suggestion to the following:

Set Opers = .Range(.Range("A2").Offset(0, iOffset + 1), .Range("A50000").Offset(0, iOffset + 1).End(xlUp).Offset(1, 0))

which does work, and which is a format I'm more familiar with.

Thanks,
Paul Hudgens