View original (French)

VBA Excel: selecting a variable range

Solved
Pyanitsa -  
 Mima -
Hello everyone,

I need to select a range of cells based on a variable i, which represents the desired row number. After researching on ccm.net and other forums, I found the following code: 

Dim MyRange As Range MyRange = Range("A" & i & ":L" & i)


I'm using Excel 2003 and something's wrong: VBA returns "runtime error 91: object variable or With block variable not set". I really don't understand. Does anyone have an idea?

Thanks in advance,
Pyanitsa.

Configuration: Windows XP / Firefox 3.6.3

7 answers

  1.
    thev Posted messages 2005 Registration date   Status Member Last intervention   721
     
    Range("A" & i & ":L" & i) represents a range of cells, that is to say an "Excel object" just like a workbook, a sheet, or a cell.
    To assign a variable (= MyRange) to an object, you need to use the SET instruction.

    That said, to assign your variable range of cells, I prefer the instruction:

    set MyRange = Columns("A:L").Rows(i)

    which I find more readable.
    32
    1. Pyanitsa
       
      Nickel, thank you very much, Thev! But why was the 'set' needed? (I'm just starting out in programming)
      2
    2. RAMIREZ
       
      Thanks a lot, I avoided being a robot thanks to that.
      0
    3. Mima
       
      Thank you very much, simple method, but effective.
      0