Concatenating your Balls

[DocAElstein]

Test



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=317218#p317218 (https://eileenslounge.com/viewtopic.php?p=317218#p317218)
https://eileenslounge.com/viewtopic.php?p=316955#p316955 (https://eileenslounge.com/viewtopic.php?p=316955#p316955)
https://eileenslounge.com/viewtopic.php?p=316955#p316955 (https://eileenslounge.com/viewtopic.php?p=316955#p316955)
https://eileenslounge.com/viewtopic.php?p=316940#p316940 (https://eileenslounge.com/viewtopic.php?p=316940#p316940)
https://eileenslounge.com/viewtopic.php?p=316927#p316927 (https://eileenslounge.com/viewtopic.php?p=316927#p316927)
https://eileenslounge.com/viewtopic.php?p=317014#p317014 (https://eileenslounge.com/viewtopic.php?p=317014#p317014)
https://eileenslounge.com/viewtopic.php?p=317006#p317006 (https://eileenslounge.com/viewtopic.php?p=317006#p317006)
https://eileenslounge.com/viewtopic.php?p=316935#p316935 (https://eileenslounge.com/viewtopic.php?p=316935#p316935)
https://eileenslounge.com/viewtopic.php?p=316875#p316875 (https://eileenslounge.com/viewtopic.php?p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

DocAElstein (http://www.excelfox.com/forum/users/9041/), 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 :)

[Excel Fox]

For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.


https://www.youtube.com/watch?v=PF8QpDhBQXQ

[DocAElstein]

DocAElstein (http://www.excelfox.com/forum/users/9041/), 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 (http://www.mrexcel.com/forum/excel-questions/797921-multiple-columns-into-single-column-using-data-text-column.html?#post3902054) ), 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

[Excel Fox]

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.


Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")


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.

[DocAElstein]

Thanks very much. I'll try that all out and keep watching this space!

[Excel Fox]

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 (http://excelexperts.com/evaluate-vba-concatenation)

Let me investigate that!

[Excel Fox]

OK, here's another thread that confirms my claim. Phew!

VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan (http://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/)

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


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.

[Excel Fox]

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.

[DocAElstein]

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!)

[DocAElstein]

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.


Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")


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 neces.......................



Hi „Fox“,
. I am working through the code again from Rick that I did not understand, getting good clued up on the exact syntax in complex Evaluation codes (A Tricky one!!!), and working through all the info you sent. I am almost there and expect to post here soon what I think would be a useful reply to this Thread. I noticed something along the way, which I do not quite understand. I applied it to your codes, (or rather some very similar) and got similar results. So while I am finishing the main reply I thought I might just post that as something to be going on with in the meantime……

. Firstly , just
. a) for clarity in the thread response editor, and
. b) so that it suits the example I am working with in regard to Rick’s code,
. – I have slightly modified the exact ranges to suit this Table (where column H is the output column and the other yellow highlighted area is my input data.


. Correspondingly your codes, modified by me, look like this:


Sub Fox1lmr()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & "&"" - ""&" & Range("A2:D10").Columns(4).Address & ")")
End Sub
Sub Fox2lmr()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & "&"" - ""&" & Range("A2:D10").Columns(4).Address)
End Sub

. Either of these codes gives the results shown in the table above.

If I modify your codes to this


Sub Fox1lm()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & ")")
End Sub
Sub Fox2lm()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address)
End Sub

. then as expected I only get the l and m values. But again both Codes work.

If I modify the codes again to this


Sub Fox1l()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & ")")
End Sub
Sub Fox2l()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address)
End Sub

. then I expect to get only the l values . BUT in fact after running both codes they both give me blank cells in column G.

. However this code


Sub Fox1lRow()
Range("H2:H10") = Evaluate("IF(ROW()," & Range("A2:D10").Columns(2).Address & ")")
End Sub

. Does give me the expected results.

. I hope to explain in my final reply that Ricks code does not require the Row() as I had initially thought. With the above results along with some very careful Syntax considerations I hope to be able to prove and explain why that is. (In the second reference you gave me I think they may have slightly “glossed over” a full explanation of this by being careful exactly what ranges they were using as well as changing them to suit as they went along!!!)

Alan

P.s. As I did some test in your Tests just now in your test forum I hit on some space / size limitation which I have not experienced in MrExcel? Can I possibly have my space, or does that come automatically when I become a “Senior” member or whatever the next bit up is??

[DocAElstein]

Try the Table again

[DocAElstein]

Ok. I have not quite got the hang of your HTML workings in Replying (It worked in Test Forum??)


Plan B:-

Here is an XL 2007 File
FileSnack | Easy file sharing (http://snk.to/f-cdkabi9h)

The table of interest is in Sheet 3 "KennyRickFox"
The corresponding Macros are in that Sheet Module

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=317218#p317218 (https://eileenslounge.com/viewtopic.php?p=317218#p317218)
https://eileenslounge.com/viewtopic.php?p=316955#p316955 (https://eileenslounge.com/viewtopic.php?p=316955#p316955)
https://eileenslounge.com/viewtopic.php?p=316955#p316955 (https://eileenslounge.com/viewtopic.php?p=316955#p316955)
https://eileenslounge.com/viewtopic.php?p=316940#p316940 (https://eileenslounge.com/viewtopic.php?p=316940#p316940)
https://eileenslounge.com/viewtopic.php?p=316927#p316927 (https://eileenslounge.com/viewtopic.php?p=316927#p316927)
https://eileenslounge.com/viewtopic.php?p=317014#p317014 (https://eileenslounge.com/viewtopic.php?p=317014#p317014)
https://eileenslounge.com/viewtopic.php?p=317006#p317006 (https://eileenslounge.com/viewtopic.php?p=317006#p317006)
https://eileenslounge.com/viewtopic.php?p=316935#p316935 (https://eileenslounge.com/viewtopic.php?p=316935#p316935)
https://eileenslounge.com/viewtopic.php?p=316875#p316875 (https://eileenslounge.com/viewtopic.php?p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

............ Correspondingly your codes, modified by me, look like this
..................................

Today I tried your exact Codes. Got the same results

[DocAElstein]

Re: EvaluatingConcatenating in VBA (and the Row() stuff)!
. Sorry if I am confusingly / mixing up Themesin this Thread!

. I will try to get some of my thoughtsstraight on this. (I might be repeating wot The “Fox” said above, but this is now how it is coming out ofmy brain now that I am beginning to understand it!). I start with a bit ofbasic Evaluate syntax as that can get a bit confusing if you are not careful!!
. We probably all know that, simplified said,the Application.Evaluate Method in VBA gives a way of using the Normal Excelfunctions in VBA. Again very simplifiedthe Evaluate in VBA sort of does wot the = does in a Normal ExcelWorksheet(Spreadsheet). So, for a randomexample, to get the value of B2 into Cell H2:
. In Excel, in cell H2, one would type
=B2
. In VBA code one would write
Sub TestVBA1()

Range("H2").Value= Range("B2").Value

End Sub

( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestVBA1()
Range("H2").Value =Range("$B$2").Value
End Sub )


… It is veryimportant to note the exact syntax, as it is easy in more complicated stuff toget confused with the number of “
……You type this Evaluate(“ “), and then in the space you type exactlywhat you would in the Spreadsheet cell but omitting the =
………In theparticular code that was giving me trouble to understand, this basic code wastaken a bit further, that is to say the Address of the cell B2 was got by the VBA code Property .Address , in our case this would look like this
Range(“B2”).Address
This code can bewritten within the Evaluate Function. The syntax for this is “ & to get into acode bit and then & “ to get out ofit!, if that makes sense!!
. So the codewould be

SubTestEvaluateVBA1()
Range("H2")= Evaluate("" & Range("B2").Address & "")
End Sub


( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestEvaluateVBA1()
Range("H2") = Evaluate(" " &Range("B2").Address & " ")
End Sub
This is because nothing is happening in thiscase or read by VBA between the pair of quotes at the start and the end!, ifthat makes sense!?)

. Staying for now with the Syntax Theme, beforeI go on to my main problem. If I wish to get involved now with another simpleexample that involves three cells, B2 and C2 And D2 the values of which I wishto put in that one cell H2, then in an Excel spreadsheet the formula syntax wemay know would be
=B2 & C2& D2
. We must be very careful now with syntax andtry to think about how Excel and VBA are thinking!. We have now 5 things beingdone after the =
. The evaluate function in VBA does allow youto “evaluate” more than one thing within a single evaluate, but just to confuseus, the & is used to link separate things! We need then our 5things within “ “ and linked by a & . Just to help us get a little less confusedlets use & for the VBA and & for the excel Spreadsheet!

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

[DocAElstein]

So theEquivalent code would be

Sub TestEvaluaten1_1n2n3()
Range("H2")= Evaluate("B2" & "&" & "C2" & "&" & "D2")
End Sub

Typically we wouldprefer to re-write the Excel Spreadsheet formula to make things a bit easier tosee:
=B2&" "&C2&" "&D2 or =B2&" - "&C2&" - "&D2 etc.

For theequivalent code we must again think like wot VBA is! After the "&" bit VBA iseffectively thinking it is in an excel Spread sheet and will except our " " , but then it needs a following "&" bit to get back out to the point where it went in.So the code is

SubTestEvaluate2_n1n2n3()
Range("H2")= Evaluate("B2" & "&"" ""&" & "C2" & "&"" ""&" & "D2")
End Sub

And to penultimately complete this bit of preliminary syntax Stuff, using that example ofa combination of Spreadsheet Functions and VBA we have correspondingly:

Sub TestEvaluateVBA1_n1n2n3()
Range("H2")= Evaluate(" " &Range("B2").Address & " " & "&"" ""&" & " " & Range("C2").Address & " " & "&"" ""&" & " " & Range("D2").Address & " ")
End Sub

I said Pen ultimately as if one carefully looks at the last Code, and againtries to think how VBA thinks, in this particular code we go out of VBA codewith & " " & inorder to use "&" , thenafter our Excel spreadsheet space " " we go back in with & " "& . This is perfectly OK and correct. However specifically here inthis example we have two blue & andeffectively ending up where we started. That takes a bit of thinking about. Butonce you get it you realize that & " " & can bereplaced in this instance with & or &
. So an Ultimate code would be
Sub TestEvaluateVBA1b_n1n2n3()
Range("H2") =Evaluate(" " &Range("B2").Address & "&"" ""&" &Range("C2").Address & "&"" ""&" &Range("D2").Address & "")
End Sub
. I personally would stick with thePenultimate. It helps me understand wot is going on.


…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

[DocAElstein]

………………
Finally gettingback to the Concatenating stuff and my original Problem:

(. Actually Iwas concatenating eventually above and used two methods: The spreadsheet methodand the VBA code method!)

. The Penultimate line could be (as it is inthe actual Code giving me problems to understand) re-written to includemultiple ranges (And in the practice is often sowritten because of speed advantages over a loop method in repeated calculations – but hat is a separate Theme notdirectly related to my problem)

SubTestEvaluateVBA3_n1n2n3()
DimRangeH3H4 As Range, RangeB3B4 As Range,RangeC3C4 As Range, RangeD3D4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
Set RangeC3C4 =Range("C3:C4")
Set RangeD3D4 =Range("D3:D4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & " "& "&"" ""&" & " " & RangeC3C4.Address & "" &"&"" ""&" & "" & RangeD3D4.Address &"")
End Sub
The above codeworks .

. It works for any number of concatenations. Going backwards forexample to just 2 numbers the code would be

SubTestEvaluateVBA3_n1n2()
DimRangeH3H4 As Range, RangeB3B4 As Range,RangeC3C4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
Set RangeC3C4 =Range("C3:C4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & " "& "&"" ""&" & " " & RangeC3C4.Address & "")
End Sub
The above code workstoo!

The code for onesimple concatenation is

SubTestEvaluateVBA3_n1()
DimRangeH3H4 As Range, RangeB3B4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & "")
End Sub

BUT IT DOES NOT WORK?!?!

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

[DocAElstein]

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!


. Now we hit on a really tricky problem…I have rackedmy brain and tried various example files and there appears to be no consistentanswer. The nearest to date is probably

……………The conditional evaluation returns an array of values, whereas adirect 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 privyto……………….
. I get the feeling nobody is "privy" to this theory. There are some ideasalong the lines…… " ….The reason is that if the Excel functionused in Evaluate does not accept an array, the Evaluate function will not returnan array…. " That seems just a general not always correct answer.
. For example, if the last Code was concerned with simple numbers,then a modification to this code results in it working. The modification issimply to multiply the nuber by 1!!
. Here the code:
SubTestEvaluateVBA3_n1_x1()
DimRangeH3H4 As Range, RangeB3B4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & "" & "*1")
End Sub

…..It works!!

. Similarly, concatenating with more complexcalculations I found not to work!?!

. Without making this thread even moreunreadable because of its length, I will only briefly touch on the theory whichsounds half believable despite not explaining all the results I have. This sayscrudely translated too everyday language something like:- Adding an IF ROW() bit ( or IF Column() bit for that matter) to theline will return a Boolean 1 which somehow forces it to work. If in ourexample we should theoretically use Row(B3:B4) but in the practice ROW() ( orColumn() ) seems to work just as well

. You must be careful again with the Syntax change for the IF bit:

Original Codeline:
= Evaluate( Original_Bit_Within_Evaluate_Brackets )

Modified codelineWith IF bit:
= Evaluate( "IF(Row()," & Original_Bit_Within_Evaluate_Brackets &")" )


. So maybe I am sort of partly there with ananswer to my original question in a long round about way, )even if along theway I almost forgot what it was!!).
. The code line originally giving me problemswas this:

FinalTableFirstColumnRange.Offset(0,1) = Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address& "&"" - ""&" &SourceTableRange.Columns(3).Address & "&"" -""&" & SourceTableRange.Columns(4).Address &")")

. Rewritten with full (The "Penultimate type") Syntax and highlighting the IF bit looks like this,

. FinalTableFirstColumnRange.Offset(0, 1) = Evaluate("IF(Row()," & " " & SourceTableRange.Columns(2).Address& " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(4).Address & " " &")")

. As it is a "simple" concatenating line the IF bit can be removed to simplify the code like this

. FinalTableFirstColumnRange.Offset(0, 1) = Evaluate(" " & SourceTableRange.Columns(2).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(4).Address & " ")

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

[DocAElstein]

. The final codeworks. Rick probably knew that. But because of the uncertainties in if when wotwith this Evaluation thing it is probably best just always to leave it in to beon the safe side. For nowthat is my answer!!!!


. Sorry to have rambled a bit. But it helped meto answer my very first Question about one line in a code from Rick. Sorry ifit confuses any other “viewers”, but maybe for any one “concatenating” in VBAor looking for a method of getting their Multiple columns in to single columnin may be a worthwhile contribution. Lets rely on the “Power of Google” to get such a person here!?

Alan Elston
Bavaria

[DocAElstein]

Hi,

VBA Evaluate Range and VLOOKUP

(This is a copy of the Thread here: P.s. I will also post this Thread Here: VBA Evaluate Range and VLOOKUP (http://www.mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup.html?#post3944034))

As a beginner I may be going a bit above my head!!

After studying and participating at some considerable length in Threads and links to do with using the Evaluate function to speed things up, I thought I understood it. Here are some of those links and Threads. (www.excelfox.com/forum/f22/concatenating-balls-1891/ (http://www.excelfox.com/forum/f22/concatenating-balls-1891/) VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan (http://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/) )

. So … the following simplified example File (XL2007 .xlsm)
https://app.box.com/s/pr78mhna00advvhsrmvi
has a Spreadsheet LEFT Function and a Spreadsheet VLOOKUP Function

The results look good! (That is to say wot I expect!) :-

[DocAElstein]

did not work see here: where it did....
http://www.excelfox.com/forum/f17/html-spreadsheet-test-1899/

[DocAElstein]

I apply this code


Sub Evaluate_Left()Dim rngName As Range
Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
Dim rngEE As Range
Set rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
Let rngEE = Evaluate("if(row(3:10),LEFT(" & rngName.Address & ",4))")
End Sub 'Evaluate_Left()

and again get wot I expact....

see again...
http://www.excelfox.com/forum/f17/html-spreadsheet-test-1899/

Now I apply this code


Sub Evaluate_VLOOKUP()Dim rngName As Range
Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
Dim rngCC As Range
Set rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
Let rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE))")
End Sub 'Evaluate_VLOOKUP()

[DocAElstein]

and get this:

see again:
http://www.excelfox.com/forum/f17/html-spreadsheet-test-1899/


. I would have expected that result without The extra If Row() stuff, which I thought overcame this problem.
. can anyone suggest wot is going wrong, or how I obtain the correct results (Using the Evaluate Function for a range)
Thanks
Alan.


P.s. I can post a spreadsheet in the test area but not apparently here in a thread??. Can you help me on that one as well?

[MARK858]

Hi DocAElstein, just as a pointer to your trouble with posting Html tables.
On vBulletin based forums there is a box in the bottom right corner that tells you what posting permissions can be used on each forum.
In this case HTML code is turned off.

http://i1371.photobucket.com/albums/ag308/samdog011/postings_zpsd2f882ba.jpg (http://s1371.photobucket.com/user/samdog011/media/postings_zpsd2f882ba.jpg.html)

[DocAElstein]

Hi DocAElstein, just as a pointer to your trouble with posting Html tables.
On vBulletin based forums there is a box in the bottom right corner that tells you what posting permissions can be used on each forum.
In this case HTML code is turned off.



Thanks.
. Any idea how I can "turn it on"?
Alan

[MARK858]

You can't, the Forum Admin decide and control what permissions are allowed in each forum.
In general in my experience it is quite general that if a forum allows attachments (like this one does) then HTML is switched off.

[DocAElstein]

You can't, the Forum Admin decide and control what permissions are allowed in each forum.
In general in my experience it is quite general that if a forum allows attachments (like this one does) then HTML is switched off.

OK.,
. I will look into the use of "attachments" next time!

Thanks
Alan