none
Why does accessing a large range of cells using the Range("").cells(row,cols) fail ? RRS feed

  • Question

  • Hi Everyone,

    I'm attempting to access a large range of cells in VBA using the range and cells functions. The range of cells spans from F6:DL33. The Cells function fails when calling:

    Dim tmp

    tmp = Worksheets("sheet1").Range("F6:DL33").Cells(15,106).value

    Excel VBA exits the function and returns "#VALUE" after the above call. There is a number in the cell and the previous cell located at Cells(15,105) works correctly.

    Is there a limit to the .Cells() function?

     

    Wednesday, January 18, 2012 6:08 PM

Answers

  • On Wed, 18 Jan 2012 18:08:11 +0000, PolarBear001 wrote:
     
    >
    >
    >Hi Everyone,
    >
    >I'm attempting to access a large range of cells in VBA using the range and cells functions. The range of cells spans from F6:DL33. The Cells function fails when calling:
    >
    >Dim tmp
    >
    >tmp = Worksheets("sheet1").Range("F6:DL33").Cells(15,106).value
    >
    >Excel VBA exits the function and returns "#VALUE" after the above call. There is a number in the cell and the previous cell located at Cells(15,105) works correctly.
    >
    >Is there a limit to the .Cells() function?
    >
     
    As far as I know, the limit would be a value that does not exceed a legal address for your worksheet.  e.g:  range("F6:DL33").cells(rows.count-range("f6:dl33").row+1, columns.count-range("F6:dl33").column+1) which would refer to, in Excel 2007, $XFD$1048576
     
    So I think your value error is being generated elsewhere in your code.
     
     

    Ron
    Wednesday, January 18, 2012 7:14 PM

All replies

  • Hi PolarBear01,

     

    Welcome to the VBA Forum.

     

    The value 106 refers to the Column postion, this is limited to 16,384 columns in Excel 2007 for example. So no problem on that part.

     

    Is "#VALUE" not the actual value of that particular Cell?

     

    When I do the test I get no errors, so the code runs fine, and the value of the Cell is returned correctly.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 18, 2012 6:30 PM
    Moderator
  • On Wed, 18 Jan 2012 18:08:11 +0000, PolarBear001 wrote:
     
    >
    >
    >Hi Everyone,
    >
    >I'm attempting to access a large range of cells in VBA using the range and cells functions. The range of cells spans from F6:DL33. The Cells function fails when calling:
    >
    >Dim tmp
    >
    >tmp = Worksheets("sheet1").Range("F6:DL33").Cells(15,106).value
    >
    >Excel VBA exits the function and returns "#VALUE" after the above call. There is a number in the cell and the previous cell located at Cells(15,105) works correctly.
    >
    >Is there a limit to the .Cells() function?
    >
     
    As far as I know, the limit would be a value that does not exceed a legal address for your worksheet.  e.g:  range("F6:DL33").cells(rows.count-range("f6:dl33").row+1, columns.count-range("F6:dl33").column+1) which would refer to, in Excel 2007, $XFD$1048576
     
    So I think your value error is being generated elsewhere in your code.
     
     

    Ron
    Wednesday, January 18, 2012 7:14 PM
  • Hi PolarBear001,

     

    How about the problem on your side?

    Are the answers given helpful?

     

    Do you still need any assistant about the problem?

    If you still show any concern on the problem, just feel free to let us know.
     

    Best Regards,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, January 24, 2012 6:59 AM
    Moderator