Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

ADLAdlAHDLAhdlakHDLadh

[DocAElstein]

I forgot about this Blog tip thread for a while.

The reason for that was that I answered a few threads, where someone asked for help because something was not doing what they wanted, and the reason for that was that they were trying to get a UDF to change the value of another cell, or something very similar.
My solution seemed to work. Sometimes the OP seemed happy. But more often than not, I got a lot of hate from some senior member, “expert Guru”, Moderator, or similar, insisting that a UDF cannot change the value of any cell other than the one it is in.

---

It’s about 2 years later now. Maybe it’s safe to post something about it again.
In the meantime, I still don’t have any newer versions of Office/ Excel, so maybe I will post a very simple example, and ask people to test it for me on newer versions. I won’t present it as "a UDF that can change the value of another cell, other than the one the UDF is in".

See how it goes.


I made a much simplified sample file, as enclosed



Feedback so far

http://www.eileenslounge.com/viewtop...ff4995#p280303





Any Other Threads doing something similar
https://stackoverflow.com/questions/...nal-formatting
https://www.mrexcel.com/board/thread...a-udf.1154593/

[DocAElstein]

Links relavent to the last 9 postsLKADHladhlAHAlhdaLDH

[DocAElstein]

Links relavent to the last 9 posts

' https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5458446
' _  First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
' _  Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
' _  I wanted to sum from column P to colum AA and transfer those sum to destination at column H
' _ Then copy from column AB to AH and paste in I to O in destination

Sub Transfer_maras_1()

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15278
https://excelfox.com/forum/showthrea...ll=1#post15279

Macro
https://excelfox.com/forum/showthrea...ll=1#post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5





_.________________________________________________ _________________________________________________

Sub Transfer_marasAlan_2() '

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15276
https://excelfox.com/forum/showthrea...ll=1#post15273


Macro
https://excelfox.com/forum/showthrea...ll=1#post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
















_.___________________________________________

Sub Transfer_marasAlan_3()  '   https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5460624

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15269
https://excelfox.com/forum/showthrea...ll=1#post15270

Macro
https://excelfox.com/forum/showthrea...ge42#post15271

Files
https://excelfox.com/forum/showthrea...ge42#post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23

[DocAElstein]

Another example, possibly, indirectly
http://www.eileenslounge.com/viewtopic.php?f=30&t=38798
This is a different problem. Indeed we want the UDF to give a result in the cell in which it is, as more typically using a UDF.
But a couple of things reminded me of discussions here…
_1) Something is not working from inside a UDF, something that otherwise works.
_2) A sudden abrupt termination, without an error, in the step ( F8 ) debug mode code execution.
_3) The thing not working is generally “wired” to interact with things in cells in a spreadsheet

So I investigated.

The first problem I had was that the thing ( DisplayFormat ) not working at all in my earlier Office/Excel versions.
But I got to check this later in 2013, https://excelfox.com/forum/showthrea...ll=1#post18404 , and confirmed the issue. DisplayFormat is working to return a value in a normal sub routine and a function, and the snag is just it working in a spreadsheet ( applying the function as available in a spreadsheet formula )

I actually won’t close to the opposite of what I was doing here.
Lets not talk about what I was doing. ( That seems controversial anyway )
Lets approach it a bit laterally thinking

What is/ was the goings on:

We got two things:

' First thing
Function WotsThereWhere(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path  & "\" & ThisWorkbook.Name & "'!UDFchangeotherCells.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "YouNameIt($A$1, ""Sheet1"")"  ' Shortened version relying on default
End Function


' Second thing
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

The first thing, Function ( used as a spreadsheet UDF) , WotsThereWhere , is on the signature line, normal looking, ( and in this case it takes an argument, a range object. )
( The only reason in this case why it is As String declared , is that it is not wanted to return anything as I was using it, at least nothing to us visible. If , for example, it was alternatively As Long declared, then I would see a zero )
The coding in that function is just one line. It Evaluates the string name of a sub routine, that sub routine is our second thing

That single code line somehow sets off the second thing , a sub routine which does what did not work directly inside the function.

That’s it

What do we conclude relevant to the current problem It seems like the thing to try is to get the second thing to
_(i) do something with DisplayFormat, and
_(ii) put the result of that ) do something with DisplayFormat in the cell where we have the UDF using the first thing function


So here we go!

Lets duplicate the first and second thing, put them in a new code module, , and give things different names more appropriate to what we are talking about, the current issues: requirements and problems
So in normal code module I name , DisplayFormatUDF, I have

Option Explicit
' First thing
Function DoSubDoDisplayFormat(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.DoDisplayFormat(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "DoDisplayFormat ($A$1, ""Sheet1"")"  ' Shortened version relying on default
End Function


' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

I remove Adeel’s formula in his cell D17, ( {=sum_color(D6:G15;C17)} ) and I replace it with =DoSubDoDisplayFormat(C17)
It seems to then give similar results to what I expect. All is well:


So lets start in the next post with the “AdeelSolution1”

---

Share ‘DisplayFormatInUDF.xlsm’ https://app.box.com/s/e4307kqrwx6zqk9uwswlpfziz6air9gy

[DocAElstein]

Links relavent to the last 9 posts

' https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5458446
' _  First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
' _  Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
' _  I wanted to sum from column P to colum AA and transfer those sum to destination at column H
' _ Then copy from column AB to AH and paste in I to O in destination

Sub Transfer_maras_1()

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15278
https://excelfox.com/forum/showthrea...ll=1#post15279

Macro
https://excelfox.com/forum/showthrea...ll=1#post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5





_.________________________________________________ _________________________________________________

Sub Transfer_marasAlan_2() '

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15276
https://excelfox.com/forum/showthrea...ll=1#post15273


Macro
https://excelfox.com/forum/showthrea...ll=1#post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
















_.___________________________________________

Sub Transfer_marasAlan_3()  '   https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5460624

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15269
https://excelfox.com/forum/showthrea...ll=1#post15270

Macro
https://excelfox.com/forum/showthrea...ge42#post15271

Files
https://excelfox.com/forum/showthrea...ge42#post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23

[DocAElstein]

ADHLhdlhd

[DocAElstein]

Links relavent to the last 9 posts

' https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5458446
' _  First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
' _  Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
' _  I wanted to sum from column P to colum AA and transfer those sum to destination at column H
' _ Then copy from column AB to AH and paste in I to O in destination

Sub Transfer_maras_1()

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15278
https://excelfox.com/forum/showthrea...ll=1#post15279

Macro
https://excelfox.com/forum/showthrea...ll=1#post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5





_.________________________________________________ _________________________________________________

Sub Transfer_marasAlan_2() '

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15276
https://excelfox.com/forum/showthrea...ll=1#post15273


Macro
https://excelfox.com/forum/showthrea...ll=1#post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
















_.___________________________________________

Sub Transfer_marasAlan_3()  '   https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5460624

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15269
https://excelfox.com/forum/showthrea...ll=1#post15270

Macro
https://excelfox.com/forum/showthrea...ge42#post15271

Files
https://excelfox.com/forum/showthrea...ge42#post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23

[DocAElstein]

Links relavent to the last 9 posts

' https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5458446
' _  First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
' _  Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
' _  I wanted to sum from column P to colum AA and transfer those sum to destination at column H
' _ Then copy from column AB to AH and paste in I to O in destination

Sub Transfer_maras_1()

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15278
https://excelfox.com/forum/showthrea...ll=1#post15279

Macro
https://excelfox.com/forum/showthrea...ll=1#post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5





_.________________________________________________ _________________________________________________

Sub Transfer_marasAlan_2() '

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15276
https://excelfox.com/forum/showthrea...ll=1#post15273


Macro
https://excelfox.com/forum/showthrea...ll=1#post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
















_.___________________________________________

Sub Transfer_marasAlan_3()  '   https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5460624

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15269
https://excelfox.com/forum/showthrea...ll=1#post15270

Macro
https://excelfox.com/forum/showthrea...ge42#post15271

Files
https://excelfox.com/forum/showthrea...ge42#post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23

[DocAElstein]

Links relavent to the last 9 posts

' https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5458446
' _  First i wanted to filter column G from workbook 1 to "Filter 2" and transfer only filter 2 data to workbook 2.
' _  Then transferring like i wanted to transfer column C (source) to column B (destination) , column D to D, Column L to Column G, etc.
' _  I wanted to sum from column P to colum AA and transfer those sum to destination at column H
' _ Then copy from column AB to AH and paste in I to O in destination

Sub Transfer_maras_1()

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15278
https://excelfox.com/forum/showthrea...ll=1#post15279

Macro
https://excelfox.com/forum/showthrea...ll=1#post15277

Files
Transfer data_marasAlan_1.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_1.xlsx : https://app.box.com/s/3d9gmeb2nlyr4gg9q802kk5mjgze8cl5





_.________________________________________________ _________________________________________________

Sub Transfer_marasAlan_2() '

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15276
https://excelfox.com/forum/showthrea...ll=1#post15273


Macro
https://excelfox.com/forum/showthrea...ll=1#post15272

Files
Transfer data_marasAlan_2.xlsm : https://app.box.com/s/749a78z2ku4m1s1tg3fvgs1z1ud4s325
Workbook2_2.xlsx : https://app.box.com/s/13yh30a77spsluauck3nif309pic8fuz
















_.___________________________________________

Sub Transfer_marasAlan_3()  '   https://www.excelforum.com/excel-programming-vba-macros/1338596-transferring-data.html#post5460624

Here is a before and after…
https://excelfox.com/forum/showthrea...ll=1#post15269
https://excelfox.com/forum/showthrea...ll=1#post15270

Macro
https://excelfox.com/forum/showthrea...ge42#post15271

Files
https://excelfox.com/forum/showthrea...ge42#post15233
Transfer data_marasAlan_3.xlsm : https://app.box.com/s/p8kf5vo8jesql3n47sd1bzgm57qdpwdv
Workbook2_3.xlsx : https://app.box.com/s/y3rwvhfk3bo1rp9t7cgbk8yz3krymh23