Initally I was trying to set the formula in all the cells in a column SheetCellRanges though I never stated that explicitly.
The problem with ranges.getCells() is that it skips empty cells, in the case I’m dealing with it returns exactly as ranges.queryContentCells(21).getCells().
When it turned out that adding a search of ranges.queryEmptyCells() and ranges.queryEmptyCells().getCells() returns nothing, perhaps as in my case the empty cells are a mix of small range and single cells.
The second search of empty cells added 50% to the search time so though it’s not as visually elegant I broke down and put zeros in those empty cells so that ranges.getCells() returns the full list.
Initially I’ve been using a rangeWalk because it hit every cell but after timing several approaches this worked fastest:
oEnum = mvColStocks.createEnumeration()
Do While oEnum.hasMoreElements() 'While there are more
oRng = oEnum.nextElement() 'Get the next component
rangeFillFormulasDown( oRng, RCformula )
Loop
Where mvColStocks is a sheetCellRanges in a column of stock market values.
I know some of you don’t like R1C1 style formulas but they are what I’m used to and dealing with formulas that change daily, it’s just easier for me using such and offsets from lastColumn.
I suspect that filling appropriate columns with namedFormulas so that I’d only have to update the formula each day rather than filling the whole column of formulas daily would be another time saver, but that’s a problem for another day.
Y’all take care,
Mike
I’ll start with thanks to Villeroy & Sokol92 for their input which greatly speeded up things.
Tha above routine averaged 729 ms over 4 runs, the below averaged 289 ms over 4 runs compared to the original rangeWalk routine that took > 2 seconds that’s huge.
I was burning midnight oil when I finally got all the kinks out of this latest or would have posted yesterday.
Dim oSrcForms()
oEnum = mvColStocks.createEnumeration()
Do While oEnum.hasMoreElements() 'While there are more
oRng = oEnum.nextElement() 'Get the next component
rStart = oRng.rangeAddress.startRow + 1
oSrcForms = oRng.getFormulaArray()
For r = LBound(oSrcForms) To UBound(oSrcForms)
For c = LBound(oSrcForms(0)) To UBound(oSrcForms(0))
oSrcForms(r)(c) = "=" & sSharesCol & rStart + r & "*" & _
sTodaysCol & rStart + r
Next c
Next r
oRng.setFormulaArray(oSrcForms)
Loop
oRng.setFormulaArray() required A1 notation so sSharesCol & sTodaysCol are A1 style letters of column numbers.
Sorry I haven’t learned how to put spaces at the front of lines to make the code easier to read.
Be well,
Mike
sokol92 showed me how to format code.
Thanks sokol92.
You may not look like a Blessing but certainly appear to be one to struggling folks like me.
Mike