Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: range(Cells(),Cells()) limitation ?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •