none
How to fill an array very quickly? RRS feed

  • Question

  • I know the sample code here used to work, but the context was different, and I haven't used this technique in a long time.  Anyway, I'm trying to avoid a loop, and I want to fill around 5578 cells really quick.  How can I do that?

            Range("E11:E5589").SpecialCells(xlCellTypeConstants).Offset(0, 0).FormulaR1C1 = _
                "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()-8,FALSE)*VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0)"
    
       

    I keep getting run-time error 1004: no cells were found.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 1:59 PM

Answers

  • If the range is empty, use

            Range("E11:E5589").Formula = _
                "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()-8,FALSE)*VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0)"

    (I don't understand why you used FormulaR1C1 to set an A1-style formula)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ryguy72 Wednesday, October 21, 2015 6:47 PM
    Wednesday, October 21, 2015 4:48 PM

All replies

  • The error "no cells were found" means that there are no cells in the range E11:E5589 that contain a constant value, i.e. that aren't empty and don't contain a formula. Is E11:E5589 empty? Or do the cells currently contain formulas?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 21, 2015 2:16 PM
  • All the cells in E11:E5589 are empty.  I want to place that formula into that array (E11:E5589).  I want to do it quickly, so I don't have to loop through thousands and thousands of cells.  Plus, that's just for ColumnE.  I've got to modify the code a bit to go several Columns to the right.  Now, I can't even get one Column working.  

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 2:26 PM
  • All the cells in E11:E5589 are empty.

    That's the reason why SpecialCells throws the error.

    Andreas.

    Wednesday, October 21, 2015 3:45 PM
  • Hummm, well I guess it make sense.  I haven't done this for a long time, but I'm sure there's a way to do it.  How do I fill that range with this formula?

    =HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()-8,FALSE)*VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0)


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 3:57 PM
  • Does it have to be done via code? If you do a 'format as table' and add the formula to the top cell in whatever column you want, when you hit <enter> the formula is automagically copied into all cells below to the end of the table boundary.

    I'm not sure if you can do this with code but try it manually first to see how it works then try to mimic with keystrokes.

    Wednesday, October 21, 2015 4:38 PM
  • If the range is empty, use

            Range("E11:E5589").Formula = _
                "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()-8,FALSE)*VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0)"

    (I don't understand why you used FormulaR1C1 to set an A1-style formula)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ryguy72 Wednesday, October 21, 2015 6:47 PM
    Wednesday, October 21, 2015 4:48 PM
  • Yeah!  This works!  Thanks for this suggestion!!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 21, 2015 6:47 PM