Concatenating your Balls

[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??