Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Cell Values from VBA
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically setting CELL COLORS based on TWO OTHER cell values | Excel Discussion (Misc queries) | |||
Setting values in a cell | Excel Discussion (Misc queries) | |||
Need Help Setting Cell Values | Excel Programming | |||
Setting Excel Cell values using C# | Excel Programming | |||
Need assist with setting cell values from an addin subroutine | Excel Programming |