Concatenating your Balls

[DocAElstein]

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

Today I tried your exact Codes. Got the same results

[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