Range Object and global failed using offset 2

[longestdrive]

Hi

I'm working on an export function from MS Project to Excel.

The export function works great. I have

dim xlrange = excel.range

and used this object numerous times throughout the code as long as range only points to once cell.

I want the xlrange object to select a number of cells and have used this code:

Set xlRange = Range(xlRange.Offset(0, -TSV.Count), xlRange.Offset(0, 0))

where TSV.count is effectively a column counter. The reason it's a minus column in the first offset is due to the current xlrange location beiung a cell on the right and i want it to select a number of cells to it's left. TSV count does stay within the boundaries of the sheet.

I get the error: Method 'Range' of object '_Global' failed.

Where am I going wrong?

set xlrange = xlrange.offset(0,1)

this works (selecting the next cell to the right), just can't get xlrange to = a number of cells

Thank you

ld

 

[PHV]

First, I'd replace this:
dim xlrange = excel.range
with this:
dim xlrange as excel.range

Then:

Set xlRange = [!]YourSheetObject.[/!]Range(xlRange.Offset(0, -TSV.Count), xlRange.Offset(0, 0))

or:

Set xlRange = [!]xlRange.Parent.[/!]Range(xlRange.Offset(0, -TSV.Count), xlRange.Offset(0, 0))

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

 

[longestdrive]

hi PHV

THANK YOU!

I typed the DIM statement incorrectly in my message - it was correct in the code.

I've added in the sheetobject and it works a treat

Thanks again

LD

 

[SkipVought]

longestdrive,

I notice that over the past 2 years, you have posted nearly a dozen times, and have received many good tips related to your stated needs. Yet, you have responded NOT ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,

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

 

[longestdrive]

Hi Skip

For a moment I didn't quite understand what you meant as I always thought I had posted a thank you within the message as I did with this one.

In a panic I've scurried through the website looking for the forum rules because I've missed a function on he site and now I've just spotted it in the footer of the reply! Aghghhh

As I said, I'm always, always grateful for the replies I receive, thanked them in the posts but missed that crucial link to 'thank' them. I'll go through my messages and update.

Thank you for pointing this out to me.

 

[SkipVought]

No problem.

Skip,

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