Page 1 of 60 1231151 ... LastLast
Results 1 to 10 of 598

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

  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

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

    fsfsf


    Copy for testing purposes July 2023

    Thread became 2909, https://www.excelfox.com/forum/showt...ing-your-Balls and then I merged this ( https://www.excelfox.com/forum/showt...L-Tables-etc-)
    https://www.excelfox.com/forum/showthread.php/2834-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)
    ) to it
    and this first post post is #post21888 https://www.excelfox.com/forum/showt...ll=1#post21888
    https://www.excelfox.com/forum/showt...alls#post21888

    Finally i changed the title from
    Copy July 2023 testing Concatenating your Balls
    to ( sort of back to )
    Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9dVEGnka6 yj
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9dVK8si3o nt
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9dVIJECZI dC
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9cLjhPi az
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g9afNBcS 4Z
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g9aLlUyz og
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9_4422N zK
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9gTXco41b 5l
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTYl6Rld pA
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfhAWU9 ju
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfuYQGm Ua
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTg3AmMP Uc
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTgEqh5w do
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49gT_8CYeQ gz
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-22-2023 at 05:27 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    DocAElstein, welcome to the ExcelFox community.

    The text to column feature in Excel is used to split text based on a delimiter, like spaces, commas (,) etc. I am almost certain that it doesn't work the other way round.

    Now, coming to your specific query about Jill referring to the Text To Column feature to convert the comma separated ball numbers for each day, in to different rows, I assure you that was just one part of the conversion. In the interest of time, and to keep that separate from the overall objective of the podcast, Jill certainly omitted to showcase the entire method he adopted to convert the data to what he was showing in that podcast video.

    What he would have done is, after pasting the comma separated data from MegaMillions site, split the data in to separate columns using Text To Column, and then used some formulae or VBA to make all data in to columns. That's my reading, and a lot of other Excel experts would agree. But, with due credit to Jill, I wish and hope that I am wrong
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.

    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by Excel Fox View Post
    DocAElstein, welcome to the ExcelFox community.

    The text to column feature in Excel.................
    Hi,
    Thanks very much for replying. Happy to be here in the Forum!.
    . I was mainly interested in a reply from Rick to my specific question on a line in his code as I had asked in #15 from the MrExcel thread ( Multiple Columns into Single Column using Data, Text to Column ), as I had not managed to catch him there.

    . But nevertheless Thanks for the interesting infomation. I expect your infomation would have been of particular interest to the initiator of that Thread. (I could possibly referrence him here, but I am not too sure about the Forum Rules about that sort of thing? Maybe you can advise me on that one?)


    . I had simply replied in that MrExcel Thread in parralel with Rick to that thread and had asked him for some clarification of one line in his code. Unfortunately it is difficult to get in touch with him.
    . Thanks again.
    . Hope I can be active as well in the future in this forum!

    Alan Elston
    Bavaria,
    Germany






    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ
    https://www.youtube.com/watch?v=jdPeMPT98QU
    https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-20-2023 at 03:04 PM.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK. The conditional evaluation returns an array of values, whereas a direct non-conditional evaluation would return a single value 'in some cases'. There could be a valid theory behind that, but that is not something I am privy to. It's probably the way EVALUATE function works. So anyway, to understand that difference a bit more clearly, you can test the following to codes. Both are actually trying to do the same time, but since the evaluate function 'in some cases' only returns a single value in the second code, the entire output in A1:A10 becomes the same. Having said that, in this case below, both the codes seem to be working fine in some systems, and not in some other systems. I will get back to you with a suitable example.

    Code:
    Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")
    Code:
    Range("A1:A10") = Evaluate(Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address)
    And by the way, you don't necessarily need to use ROW()

    Rick just used that as a means to move the IF function's execution to the TRUE condition. Now, you would know that anything except 0 (zero) means TRUE in the Excel boolean world. And since the minimum value of ROW() is greater than 0 (yes, it's 1 and above), using ROW(), or using 1, has the same effect in the IF condition.

    Hope this makes it clear.

    EDIT: Will get back with more clarity.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Thanks very much. I'll try that all out and keep watching this space!


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
    https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
    https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839tUQl_92m vg
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg.9isY3Ezhx4j9itQLuif2 6T
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg.9irSL7x4Moh9itTRqL7d Qh
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg.9iraombnLDb9itV80HDp Xc
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg.9is0FSoF2Wi9itWKEvGS Sq
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdsh r6
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9z XI
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_ 3f
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11 nH
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOe Xa
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpT G8
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-13-2023 at 10:58 AM.

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's another sample by Vishesh. There also he is using a similar logic. But when I tested it on my machine, the code works fine even if I remove the IF condition.

    Evaluate (VBA) for Concatenation | ExcelExperts.com

    Let me investigate that!
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK, here's another thread that confirms my claim. Phew!

    VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan

    Specifically, read the below excerpt


    Suppose we’ve some text values in range A1:A10 and we want to extract and keep only the first three letters of the values in all cells of this range. We could try to do so using the below code:
    Code:
        Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
        rngData = Evaluate("Left(" & rngData.Address & ",3)")
    BUT, you’ll find it does not work. It will fill the whole range with first 3 letters of cell A1. The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array. So in order to make this function return an array we need to modify the code slightly like this:

    Code:
        Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
        rngData = Evaluate("if(Row(1:10),left(" & rngData.Address & ",3))")
    In this case, ROW(1:10) returns an array of numbers from 1 to 10. Any numeric value other than 0 returned from a logical function is considered as TRUE, So there are 10 vertical TRUE values. For each TRUE, it will return the corresponding cell’s value from A1:A10.
    And the reason why it was working in the previous examples was probably because we weren't using any other function within the EVALUATE function. Hopefully, this makes more sense.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Wow, great, Thanks for all that info. - I'll work me way throught it now (or maybe tomorrow night in my daily "VBA Hour" - It is nearly bed-time now here in Bavaria)

    Thanks again
    Alan

    .P.s....
    By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.
    ...OK....(should be OK - Rick referrences this Forum in all his replies!)

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
  •