User defined function not working properly

**xibo8748** · 01-10-2017, 08:52 PM

Nice to meet all the fellows.

I am new to excel Macro VBA, and I just tried to write my first VBA code for the user defined function, however, its not 100% working as the way I would like it to.

I am trying to create a formula to copy the value of next right cell into current activecell, and if the next right cell is blank, copy the value from the one above of the current activecell. (like the screenshot below, the yellow part is the result I want)

Name: Capture.JPG Views: 0 Size: 28.2 KB

Below is my vba code for the function

Function CopyfromNext(x As String) As String
If x <> "" Then
CopyfromNext = ActiveCell(1, 2).Value
Else
CopyfromNext = ActiveCell(0, 1).Value
End If
End Function

It is working fine if I run the code from the vba editor ( running the function each time separately for each cell).
However, if I run the function from the excel function list (just like the excel build in functions) and I drag and copy the formula down to the column ( like the way people normally run for the vlookup), it is not give me correct value (my excel in auto calculation).

Could anyone help me to find why that, I really can’t figure out.

Thanks

**mikerickson** · 01-10-2017, 08:57 PM

No need for a UDF

=LOOKUP("zzzzz",E$1:E2)

**xibo8748** · 01-10-2017, 09:18 PM

Thanks Mikerrickson for your reply.

however, I am still not sure why my UDF not working properly.

Do you think you could kindly look my code, is there anything not correct?

Thanks

**MrShorty** · 01-10-2017, 10:38 PM

This forum can be strict about rules like putting code in code tags. Edit your OP to include the code tags, lest someone come along and lock your topic.

As a thought to prod your thinking, what does "ActiveCell" mean when a function is called from a spreadsheet cell? Note in particular that ActiveCell does NOT mean "the cell calling the UDF" or anything like that. For a UDF intended to be called from a spreadsheet cell, I would not ever expect to use ActiveCell.

As an introductory practice into debugging tools and strategies, Put a Stop statement at the top of your UDF, enter the function into a cell, then add a watch for "ActiveCell.Address" or execute debug.print activecell.address in the immediate window or something to help you see what is the ActiveCell.