C
Casteele/ShadowLord
I'm using Excel 2003, and trying to figure out how to set a number of cells
(from VBA) in a worksheet to specific values based on the contents of another
cell. Specifically..
I have one column with the raw data values. The next few columns over, I
need to generate the results of some calculations on each row of the first
column. So far, that's the easy part.. The hard part is, the results can
include anywhere from 1 to 10 values.. So what I'm trying to do is write a
function that returns the first result in the cell with the function
(=myFunc(RC[-1])), but it also needs to write any remaining results to the
next 2 thru 10 columns.
Because the calculations are complex, and there's a couple thousand rows to
process, I'd rather not have to run the same computations repeatedly for each
column just to extract one value from the set.
So far, I've tried something like:
<code>
Function myFunc(nData As Integer, thisCell As Range) As Integer
Dim nResults(1 To 10) As Integer
Dim i, j
' (lengthy processing code snipped.. it basically puts the results in
the nResults array, and store the number of results generated in j)
myFunc = nResults(1)
For i = 2 To j
If nResults(i) > -1 Then
thisCell.Offset(0, i).Value = nResults(i)
Else
thisCell.Offset(0, i).Value = "#ERR"
End IF
Nexy i
End Function
</code>
When calling, the formula I'm using is "=myFunc(RC[-1],RC)", however, Excel
prints the "#VALUE" error value in any cell where there's more than one
result. What am I doing wrong?
Thanks,
C.
(from VBA) in a worksheet to specific values based on the contents of another
cell. Specifically..
I have one column with the raw data values. The next few columns over, I
need to generate the results of some calculations on each row of the first
column. So far, that's the easy part.. The hard part is, the results can
include anywhere from 1 to 10 values.. So what I'm trying to do is write a
function that returns the first result in the cell with the function
(=myFunc(RC[-1])), but it also needs to write any remaining results to the
next 2 thru 10 columns.
Because the calculations are complex, and there's a couple thousand rows to
process, I'd rather not have to run the same computations repeatedly for each
column just to extract one value from the set.
So far, I've tried something like:
<code>
Function myFunc(nData As Integer, thisCell As Range) As Integer
Dim nResults(1 To 10) As Integer
Dim i, j
' (lengthy processing code snipped.. it basically puts the results in
the nResults array, and store the number of results generated in j)
myFunc = nResults(1)
For i = 2 To j
If nResults(i) > -1 Then
thisCell.Offset(0, i).Value = nResults(i)
Else
thisCell.Offset(0, i).Value = "#ERR"
End IF
Nexy i
End Function
</code>
When calling, the formula I'm using is "=myFunc(RC[-1],RC)", however, Excel
prints the "#VALUE" error value in any cell where there's more than one
result. What am I doing wrong?
Thanks,
C.