UDF that can change values in cells other than the cell in which the UDF is used

[DocAElstein]

Question from the web[ ( https://www.excelforum.com/excel-pro...udf-error.html )

UDF error ? ( (UDF) add formula to a cell (find the last used cell in a column) )

The formula initially discussed in the first post of that Thread, at excelforum.com is possibly based on this one from the web/excelfox
____________=LOOKUP(1.1,1/(C:C<>""),C:C)
https://www.excelfox.com/forum/showt...ll=1#post27585 https://www.exceldemy.com/excel-find...lue-in-column/
https://www.excelfox.com/forum/showt...-in-a-column*)

( Here is the approximate equivalent version used in that excelforum Thread
____________=LOOKUP(2,1/(C[1]<>""),C[1])
We will discuss that equivalent more later, but just one passing important fact to hear for the first time: The C in those formulas are not the same:
In the first formula it is referring to the actual column C, the third column along to the right in an Excel spreadsheet. ( It was unfortunate I chose to use C in developing that formula, it was a bit silly really as it could confuse. Any column other than C or R would have been better )
In the second formula the C itself is referring to columns in general, and usually a number associated with it tells us which column. (In that particular formula, it is the next column to the right of where the formula is written – 1 column to the right as it were) )


The typical wrong answers, (as for example given initially in that excelforum thread)
A UDF cannot add a formula to a cell.
A UDF is a function that returns a value in the cell where it is called . …. A UDF cannot change the contents of any cell.

---

I finally answered here in four posts, this one, #21, and then the following 3, #22, #23, #24.
I am covering what I see as the three mains issues.
This first post Post #21 https://www.excelfox.com/forum/showt...ll=1#post27596 is a bit of background and rambling from me
, the meat of my answer is in the following three posts,
Post #22 https://www.excelfox.com/forum/showt...ll=1#post27597
Post # 23 https://www.excelfox.com/forum/showt...ll=1#post27597
Post # 24 https://www.excelfox.com/forum/showt...ll=1#post27599

---

The Full Question

I have a formula, that works, to find the last cell in a column: =Lookup(2,1/(rngInput<>""),rngInput)
I used the macro recorder to produce the following which also works: 

	
	
Sub Macro1()
    ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(C[1]<>""""),C[1])"    
End Sub
I have tried to adapt the macro to create a UDF:

	
	
Function LastValue(rngInput As Range)
Dim WorkRange As Range
 Set WorkRange = rngInput.Columns(1).EntireColumn
 ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(WorkRange<>""""),WorkRange)"
End Function
The function fails with the error #VALUE

---

---

So, I can see approximately three main issues to consider.
_ (i) Possibly an issue of using a UDF to add a formula to a cell. Often regarded as impossible, as per the wtong answers referenced. But I figured it out a few years back, in 2018, ( in this main Thread, ( the one I am posting in now ) . ) So we can do that, and ignore the first few replies in the excelforum.com Thread, treating them as misleading rubbish
_ (ii) An issue of the correct formula syntax, not necessarily the problem that the original question poster , Marnhullman, thought h had. But there are some syntax problems.
_ (iii) An issue that the helpers at excelforum.com went off in their usual frenzy of trying to get as many quick posts in as possible and may have missed the point and therefore complicated the issue making it difficult for the original question poster , Marnhullman, to easily follow. (Added to that we have the usual annoying over zealous nauseating Moderator contributions from Moderator AliGW, who/which seems to get on everyone’s backs apart from a few almost as mad senior members, Moderators, Admis, etc. who in the meantime have become her obedient minions or her good little school children..)


I will address the 3 issues in the reverse order

_(iii) What is wanted.
This is all a bit of an aside, and some ramblings from me

I cannot be sure either of exactly what is wanted, but we don’t have the distraction of the over zealous Hacks here at excelfox, so we may get slightly closer, even if it takes longer. Quality and imparting knowledge, furthering the subject, not racing to get as many short answers in for AI to learn from so as to help destroy Human thinking.

A bit of background:
Now, the term UDF, (User Defined Function), is not a term whose definition is fixed in stone. It usually means something along the lines of like this example: ….

Simple example of what UDF usually is about
We will follow the steps of somebody making himself a UDF….
_"....There is not a built in function/ formula in Excel to do what I want. So I will make one., like this example:
Say, I want to say Hello in a specified language…..

So I put this coding in a normal code module

Public Function Hi(ByVal Langwidge As String) As String
    If Langwidge = "German" Then
     Let Hi = "Guten Tag"
    ElseIf Langwidge = "English" Then
     Let Hi = "Hello"
    Else
     Let Hi = "Huh?"
    End If
End Function

Now, if I go into an Excel Spreadsheet and type in any cell this
=Hi("English")
, then, after hitting Enter I will see in that spreadsheet cell the following
Hello
, and in the formula bar I will see what I typed
=Hi("English")

https://imgur.com/wO6lh6E ____ https://i.postimg.cc/859FdcdN/Hi-English.jpg





So that was just an example of what a UDF usually is regarded as, or what a UDF is talking about.

But the UDF definition is not set in stone. You might just call all that a custom function, or a user written function.
Occasionally the term UDF might be used a bit loosely for any coding a user might write.
Instead of coding we might say a macro or a procedure

That was all a bit of an aside

---

.

It is not completely clear if Marnhullman knows exactly what best he wants, which would be normal, as Excel and VBA is so vast and diverse that there are usually a lot of different ways to do anything.

But I think I have approximately covered issue _( iii )





Now, My go at answering the question from here would be:
I would tend to keep initially to
_ variations of the formula, that Marnhullman said works, that being the formula to find the last cell in a column,
, and
_ the macro recording that Marnhullman said works
That would approximately cover issue _(ii)

, then I would take it from there, considering approximately issue _(iii) as last. issue _(iii) is what this main tutorial Thread is about, but it us a bit advanced and we won't lt ourselves get tunnel versioned for the sake of cold neatness and order.





I will move on to the second issue _(ii) in the next 2 posts. That is mainly a discussion of the correct syntax for putting a formula in a cell. We need to get that correct bfore we do anything more advanced.