Concatenating your Balls

[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