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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Cell Values from VBA
A user defined function can only change the value in the cell it's called
by. You can return an array from a function, but you have to select enough cells, and array-enter the function into the range of cells (CTRL+SHIFT+ENTER). Set up your UDF so it generates an array of 10 values, and leave the unused ones blank. The code for the function should populate the array. Select a 10 column wide row of cells, type the formula, and hold CTRL and SHIFT while pressing ENTER. If done correctly, Excel surrounds the formula in curly brackets: {=MYFUNC(RC[-1])} If you type the curly brackets, it will not work. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Casteele/ShadowLord" wrote in message ... 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. |
Reply |
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 |