What is wrong with this range???

[Geoff ML]

NextGameCounter, x and y are all integers. GoalieX is defined as an object.
AwayTeamPage is defined as a worksheet and is used several times earlier in
code without any issues.

i get the error message Method 'Range ' of object '_Worksheet' failed. i
don't get it.

For y = 1 To 9
Set GoalieX = AwayTeamPage.Range("GOALIE" & y)
For x = 1 To 3
AwayTeamPage.Range(GoalieX).Cells(4 + NextGameCounter, -3) =
AwayTeamPage.Range("g6").Cells(NextGameCounter, -1 + x)
Next x
Next y

 

[Tom Ogilvy]

so you have defined names/named ranges Goalie1 to Goalie9

then

For y = 1 To 9
Set GoalieX = AwayTeamPage.Range("GOALIE" & y)
For x = 1 To 3
GoalieX.Cells(4 + NextGameCounter, -3) = _
AwayTeamPage.Range("g6").Cells(NextGameCounter, -1 + x)
Next x
Next y

should work although I don't see NextGameCounter getting changed within your
loop and GoalieX must refer to a location in column D or farther to the
right.

 

[Geoff ML]

thanks a lot

 

[sbmack7]

NextGameCounter, x and y are all integers. GoalieX is defined as an object.
AwayTeamPage is defined as a worksheet and is used several times earlier in
code without any issues.

i get the error message Method 'Range ' of object '_Worksheet' failed. i
don't get it.

For y = 1 To 9
Set GoalieX = AwayTeamPage.Range("GOALIE" & y)
For x = 1 To 3
AwayTeamPage.Range(GoalieX).Cells(4 + NextGameCounter, -3) =
AwayTeamPage.Range("g6").Cells(NextGameCounter, -1 + x)
Next x
Next y

If "Goalie" is a named range then Range("Goalie" & y) is messed up.
And why declare GoalieX as an object? Using that downstream is messed
up too. I'd tell you more, but it's not clear to me what you are
trying to do.

SteveM