Rory – I just opened up Ozgrid and saw your post as I was copying and pasting the following which I set up in a Word document. I agree; it’s a good idea.
Hello, again. I think I have a solution, inspired by your macro. After looking at it again, it occurred to me to put something like it in my own macro. Here is the original action part of my macro:
Dim r As Range
indx = 1
For Each r In Worksheets("Nutrients Consumed").Range("odd-cells named range").Offset(0, j)
r.Value = r.Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r
r and k are the only variables pertinent to our concern; the others have to do with where the data comes from. Here is the modification:
Dim r As Integer
Dim k As Range
Set k = Worksheets("Nutrients Consumed").Range("BaseRng")
For r = 1 To k.Rows.Count Step 2
k.Cells(r, 1).Offset(0, j).Value = _
k.Cells(r, 1).Offset(0, j).Value + (arstrt(1, indx) / 100) * NewValue
indx = indx + 1
Next r
In other words, instead of looping through every cell in the new odd-cells named range, I simply loop through every odd cell in BaseRng and skip the named range altogether. Any suggestions for improvement are welcome. Also, for future reference I’d still like to know if all array formulas will not operate for a Named Range definition, or just the one I was trying to use and why.
Sorry, everyone. I forgot to push "reply" for the above. The following I just wrote. Thanks for your patience.
Hi, Carim. Attached is a small imitation model of my project that you asked for. I would like to keep the real information under wraps for now, so this is a pretend tool for calculating genetic content of one drop of blood from various animals. (This is the phoniest science you’ll encounter this week.) Numbers in “Input” are multiplied by numbers in “Data”, and the results are displayed in the appropriate cells in “Genetic content”. The message boxes are for testing and not part of the final workbook. Let me know if anyone sees where I could make improvement.
I tried copying your formula (from post #9) to a cell, and it returns only the first value. The formula from post #12 returns all the correct values (horizontally), but I can’t get it to work in a range name.
Thanks, Pecoflyer, for your post. Sorry, but I just don’t understand arrays yet. I’ve read several times that CSE is no longer necessary. But I read just as often that it is. In any case, I want others to be able to to use this on their own machines. And what is AFAIK? Thanks for you help.
And thank you, Rory. The idea is working beautifully. But I’m not sure what you mean in #18. It’s been my experience that many formulas, when used on a worksheet, return values. When those same formulas are used as range name definitions, they identify the ranges those values occupy.
Excel forum question sample NIC.xlsm