VB Macro with more than one output

[Greedo]

I have made a VB macro that has more than one output but I am having trouble getting the two outputs into the correct cells. I have one cell whose formula is =Function(A, B). That one works fine but the Function also needs to put data into a cell that is two cells to the right (ie, Function is supposed to output data to both B5 and D5 and only B5 gets anything). I get data from the spreadsheet using the line:

windim(Direction, Direction) = Sheet3.Cells(20, 2).Value

Or something similar but I can't get the macro to output data in a similar way. I hope this question is clear, if not, let me know.

 

[xlbo]

Not quite clear here - are you trying to enter data from a function within CODE (a macro) or from a worksheet UDF (User Defined Function)

If it is the latter, you're out of luck - UDFs cannot change the state of a worksheet - thay can only return an answer to the cello that they are entered into

If it is via CODe, can you post the code you are using.. Rgds
~Geoff~

 

[Greedo]

ok, its that thing that means im out of luck.

to get around this then do i have to have a second macro that is identical to the first but has the second bit as the inportant part? do i need to change varialbe names or anyhting?

 

[xlbo]

The only way to return an answer to a cell via a UDF is to place the UDF in that cell

can you post the code that makes up the function ??

One suggestion would be that you use an IF statement to determine which answer to return

Say the formula is called MYFORMULA and is called by using
=MYFORMULA(A,B)

if mid(activecell.offset(0,2).formula,2,9)="MYFORMULA" then
'return answer for B5
else
'return answer for D5
end if Rgds
~Geoff~