range(Cells(),Cells()) limitation ?

[Andrew XJ]

Urgent!
Could any one tell whether there is a limit on the number of variables in Range(). I have the following codes:
Worksheets("sheet1").Activate
Range(Cells(iShift * 21 - 16, iCol), Cells(iShift * 21 - 5), iCol).ClearContents

But it always pops up a message to tell me
"Compile Error. Wrong Number of arguments or invalid property assignment"

Could you fix this problem for me.
Thanx

regards
Andrew XJ

 

[Ivan F Moala]

On 2002-03-14 21:37, Andrew XJ wrote:
Urgent!
Could any one tell whether there is a limit on the number of variables in Range(). I have the following codes:
Worksheets("sheet1").Activate
Range(Cells(iShift * 21 - 16, iCol), Cells(iShift * 21 - 5), iCol).ClearContents

But it always pops up a message to tell me
"Compile Error. Wrong Number of arguments or invalid property assignment"

Could you fix this problem for me.
Thanx

regards
Andrew XJ

Should be.....Just check number of brackets
as the open brackets count "(" should match
the closed bracket count ")"
Range(Cells(iShift * 21 - 16, iCol), Cells(iShift * 21 - 5), iCol)).ClearContents

Ivan

 

[Andrew XJ]

I use VB Editor. The "(" and ")" are paired. I have checked that.
I don't know whether the number of parameters can be used in Cells() if Cells() itself is property of Range().
Or any other problems.
I have declared the variables.

 

[Ivan F Moala]

On 2002-03-14 22:14, Andrew XJ wrote:
I use VB Editor. The "(" and ")" are paired. I have checked that.
I don't know whether the number of parameters can be used in Cells() if Cells() itself is property of Range().
Or any other problems.
I have declared the variables.

sorry I had the brackets wrong
should be;

Range(Cells(iShift * 21 - 16, iCol), Cells(iShift * 21 - 5, iCol)).ClearContents


Ivan

 

[Andrew XJ]

Thx. Now it works. The number is correct, but the positions are not correct.
This is a problem about Range again. So sorry to trouble you again.
Code:
weekRow = Range("A302:A353").Find(what:=week, lookAt:=xlWhole, searchOrder:=xlByColumns).Row

The error message:
Object Variable or With Block variable not Set.
I have selected both worksheet and the range.

 

[Ivan F Moala]

On 2002-03-14 23:35, Andrew XJ wrote:
Thx. Now it works. The number is correct, but the positions are not correct.
This is a problem about Range again. So sorry to trouble you again.
Code:
weekRow = Range("A302:A353").Find(what:=week, lookAt:=xlWhole, searchOrder:=xlByColumns).Row

The error message:
Object Variable or With Block variable not Set.
I have selected both worksheet and the range.

Try
Dim weekRow as range
dim iWkRow as double
set weekRow = Range("A302:A353").Find(what:=week, lookAt:=xlWhole, searchOrder:=xlByColumns)

iWkRow = weekRow.Row

you will probably need an error handling routine incase nothing is found in which case the variable weekRow = Nothing


Ivan