Page 42 of 54 FirstFirst ... 32404142434452 ... LastLast
Results 411 to 420 of 604

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    ADLAdlAHDLAhdlakHDLadh

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    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/
    Attached Files Attached Files

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 postsLKADHladhlAHAlhdaLDH

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 posts

    Code:
    ' 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





    _.________________________________________________ _________________________________________________




    Code:
    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
















    _.___________________________________________

    Code:
    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

  5. #5
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    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:
    Code:
    ' 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
    Code:
    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

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 posts

    Code:
    ' 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





    _.________________________________________________ _________________________________________________




    Code:
    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
















    _.___________________________________________

    Code:
    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

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    ADHLhdlhd

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 posts

    Code:
    ' 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





    _.________________________________________________ _________________________________________________




    Code:
    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
















    _.___________________________________________

    Code:
    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

  9. #9
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 posts

    Code:
    ' 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





    _.________________________________________________ _________________________________________________




    Code:
    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
















    _.___________________________________________

    Code:
    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

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,468
    Rep Power
    10
    Links relavent to the last 9 posts

    Code:
    ' 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





    _.________________________________________________ _________________________________________________




    Code:
    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
















    _.___________________________________________

    Code:
    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

Similar Threads

  1. Testing Concatenating with styles
    By DocAElstein in forum Test Area
    Replies: 2
    Last Post: 12-20-2020, 02:49 AM
  2. testing
    By Jewano in forum Test Area
    Replies: 7
    Last Post: 12-05-2020, 03:31 AM
  3. Replies: 18
    Last Post: 03-17-2019, 06:10 PM
  4. Concatenating your Balls
    By DocAElstein in forum Excel Help
    Replies: 26
    Last Post: 10-13-2014, 02:07 PM
  5. Replies: 1
    Last Post: 12-04-2012, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •