…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
Quote Originally Posted by ExcelFox View Post
……………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!