Using array variable to populate range

Thanks for the response Roy,

the rws variable in the loop is defined here:

Sheets("Raw Data").Activate
rws = Range(Range("A1"), Range("A1").End(xlDown)).Rows.count

Sorry if I'm having a "thick" day, but is it not set here?

ReDim TempArray(1 To rws) 'set the size of the array
Set TheRange = Range(Range("A2"), Range("A" & rws)) 'define the destination range
For iCount = 1 To rws
TempArray(iCount) = "=IF('Raw Data'!R[58]C="""","""",'Raw Data'!R[" & 57 + iCount & "]C)" 'fill the array
Next iCount
TheRange.FormulaR1C1 = TempArray 'transfer the array to the sheet

Thank goodness for that - I thought I was being dafter than usual!

It's a mystery to me, but if you're not wedded to your current approach this seems to work:

Set TheRange = Range(Range("A2"), Range("A" & rws)) 'define the destination range
TheRange.FormulaR1C1 = "=IF('Raw Data'!R[58]C="""","""",'Raw Data'!R[" & 57 + iCount & "]C)"

EDIT: on reflection the double increment may be due to the fact that Excel automatically adjusts formulae across a range, added to which you are incrementing in the loop.

Thanks all,

I'm using the array approach because it's quicker. There's over 45000 cells to populate, and using a "normal" loop to write to the sheet every time takes 3-4 minutes.

Any ideas on a workaround?

Arrays are easily written to a range using the Range's Resize property.

Whether you use an array method or another, I would recommend that you turn off calculation, screen updates and events as I did in http://vbaexpress.com/kb/getarticle.php?kb_id=1035

Thanks Kenneth,

I have events, calculations and screenupdate turned off.

Could you further explain the resize method wrt arrays?

Sorted it out. Quickest method I could find was using range.formula for the first line and then range.autofill for the additional.

Thanks all.

Hi Roy,

Thanks, but I still get a double increment in iCount in the spreadsheet. Not to worry - I'll make do with the autofill method.

Thanks to all,

Dave